How to generate a CSV file from SQL*Plus – Part 1, by Zahar Hilkevich

SQL Plus was widely used for reporting purposes for years but not any more. However, it can still be very handy if we want the report to be opened in another software product, such as Microsoft Excel.
In this post, I will show you how you can generate an Excel file directly from SQL*Plus.

Let’s say, for simplicity sake, we need to dump the result for the following query in Excel (csv) file:

SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
GROUP BY dept.deptno, dept.dname
ORDER BY 1

The following script will help you accomplish the task:

accept fname prompt "Please enter full report file name: "

SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF
SET ECHO OFF
SET LINESIZE 100
SET PAGESIZE 0
SET VERIFY OFF

spool &fname 

DECLARE
   CURSOR c IS
   SELECT dept.deptno, dept.dname, COUNT(emp.empno) emp_count
   FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno
   GROUP BY dept.deptno, dept.dname
   ORDER BY 1;

BEGIN
   DBMS_OUTPUT.ENABLE(NULL);

   --Dump out the header line:
   DBMS_OUTPUT.PUT('Dept No,');
   DBMS_OUTPUT.PUT('Dept Name,');
   DBMS_OUTPUT.PUT_LINE('Emp Count');

   --Dump out the records:
   FOR v IN c LOOP
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
END;
/

spool off

The content of the generated file should look like this:

Dept No,Dept Name,Emp Count                                                                         
"10","ACCOUNTING","3"                                                                               
"20","RESEARCH","5"                                                                                 
"30","SALES","6"                                                                                    
"40","OPERATIONS","0"       

Here is how you run it from SQL*Plus (after establishing the connection):

r2

r

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Part 2 is here

Advertisements

One thought on “How to generate a CSV file from SQL*Plus – Part 1, by Zahar Hilkevich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s