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):
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.