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

This is a logical continuation of the topic we touched in a previous post.

This time we will see how to generate a CSV file from a cursor returned by a stored procedure. Our procedure should simply return a ref cursor based on the same query we used before:

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

Here is the code for such stored procedure:

CREATE OR REPLACE PROCEDURE sp_get_dept(p_result OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_result FOR
    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;
END;

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
   c SYS_REFCURSOR;
   --The following cursor is needed only to quickly define the type of the variable v;
   CURSOR c_type IS
   SELECT deptno, dname, 100 AS emp_count
   FROM dept
   WHERE 1=2;

   v c_type%ROWTYPE;
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');
  
   --Call stored procedure
   sp_get_dept(c);   

   --Dump out the records:
   LOOP
        FETCH c INTO v;
        EXIT WHEN c%NOTFOUND;
	DBMS_OUTPUT.PUT('"' || v.deptno || '",');
        DBMS_OUTPUT.PUT('"' || v.dname || '",');
        DBMS_OUTPUT.PUT_LINE('"' || v.emp_count || '"');
   END LOOP;
   CLOSE c;
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"       

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

Advertisements

One thought on “How to generate a CSV file from SQL*Plus – Part 2, 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