Generating Excel files using PL/SQL
Over the last decade or so I've written innumerable SQL queries in response to ad-hoc data requests from business users. As Excel is ubiquitous on the corporate desktop, users generally ask that the results be sent to them in an Excel file. Providing there are only a few datasets involved, the easiest approach is to use SQL followed by a cut-and-paste. But what if there are several datasets to be generated, each one differing from the others only in a query parameter or two? It would be stupendously sub-optimal, not to mention patently painful, to use the cut-n-paste method. Fortunately the task can be done programatically using PL/SQL and the built-in UTL_FILE package. This article demonstrates how.
Let's get some preparatory work out of the way first. The demonstration uses the EMP table that comes with the SCOTT schema in Oracle. A script to create and populate all tables in the SCOTT schema can be found at <ORACLE_HOME>/sqlplus/demobld.sql, where <ORACLE_HOME> is the Oracle home (installation) directory. Note that you'll need only SCOTT.EMP for this demo.
You will also need a couple of system privileges (which can be granted only by someone with DBA privileges). The required privileges are: CREATE PROCEDURE, and the ability to read/ write to an operating system directory from within Oracle. CREATE PROCEDURE is a straightforward Oracle system privilege so no more will be said about it. Read/write privileges on an OS directory can be granted in one of two ways. The first is to create an Oracle directory object like so:
create directory reports as 'C:\reports'This creates an Oracle directory object called "reports" which points to the OS directory "C:\reports". The demo schema then needs to be granted access to the directory. This is done via the following SQL:
grant read, write on directory reports to scottassuming the demo schema is SCOTT. The second (and deprecated) way to grant OS directory access is to set the oracle initialization parameter UTL_FILE_DIR to the directory name ('C:\reports' in the demo). Assuming you are using a stored parameter file (spfile), the command to do this is:
alter system set utl_file_dir='C:\reports' scope=spfileNote that you will need to bounce (restart) the database for the new setting to take effect.
As mentioned earlier the first method is preferred, and is the one that will be used in this demonstration.
Generating Excel files from the database:
The application requirement is as follows: for each manager in the EMP table, generate an Excel file containing a list of direct reports with name, hire date and salary. The output files should be written to c:\reports.
The solution is achieved through a PL/SQL procedure, which I'll first list and then deconstruct. Here it is:
CREATE OR REPLACE procedure print_reports isThe cursor c_mgr returns the list of manager names and employee numbers. The latter are fed, via the cursor variable cv_mgr, into the cursor c_direct_reports which in turn generates the employee list for the manager.
cursor c_mgr is
cursor c_direct_reports (cv_mgr number) is
v_wstring varchar2 (100);
for r_mgr in c_mgr loop
v_file := r_mgr.ename||'_direct_reports_'||v_date||'.xls';
wfile_handle := utl_file.fopen ('REPORTS',v_file, 'W');
for r in c_direct_reports(r_mgr.empno) loop
v_wstring := r.empno||chr(9)||r.ename||chr(9)||r.job||chr(9)||to_char(r.hiredate,'dd/mm/yyyy')
In the variable v_header, I've concatenated the system date and manager name to generate a unique filename for each manager's report. The .xls extension ensures that Excel can recognise the file.
The built-in package UTL_FILE is used to access the OS directory (follow the link for brief package documentation). We use three package procedures as follows:
This technique is particularly useful when one has to generate a large number of reports that differ only in query parameters. In one instance I used it to generate activity reports for 200 sales representatives. The procedure ran in less than two minutes. I shudder to think how long it might have taken, and how error ridden the results might have been, had I used a cut-and-paste approach.
Back to the top