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

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