|
|
|
|
|
|
|
|
Generating Excel files using PL/SQL
Introduction:
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.
Preliminaries:
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 scott
assuming 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=spfile
Note 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 is
cursor c_mgr is
select
t1.ename,
t1.empno
from
emp t1
where exists
(select
'x'
from
emp t2
where
t1.empno=t2.mgr);
cursor c_direct_reports (cv_mgr number) is
select
empno,
ename,
job,
hiredate,
sal
from
emp
where
mgr=cv_mgr;
wfile_handle utl_file.file_type;
v_wstring varchar2 (100);
v_header varchar2(100);
v_file varchar2(100);
v_date varchar2(20);
begin
select
to_char(sysdate,'dd_mon_yyyy')
into
v_date
from
dual;
v_header :='empno'||chr(9)||'ename'||chr(9)||'job'||chr(9)||'hiredate'||chr(9)||'sal';
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');
utl_file.put_line(wfile_handle,v_header);
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')
||chr(9)||r.sal;
utl_file.put_line(wfile_handle,v_wstring);
end loop;
utl_file.fclose (wfile_handle);
end loop;
end print_reports;
The 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
select
t1.ename,
t1.empno
from
emp t1
where exists
(select
'x'
from
emp t2
where
t1.empno=t2.mgr);
cursor c_direct_reports (cv_mgr number) is
select
empno,
ename,
job,
hiredate,
sal
from
emp
where
mgr=cv_mgr;
wfile_handle utl_file.file_type;
v_wstring varchar2 (100);
v_header varchar2(100);
v_file varchar2(100);
v_date varchar2(20);
begin
select
to_char(sysdate,'dd_mon_yyyy')
into
v_date
from
dual;
v_header :='empno'||chr(9)||'ename'||chr(9)||'job'||chr(9)||'hiredate'||chr(9)||'sal';
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');
utl_file.put_line(wfile_handle,v_header);
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')
||chr(9)||r.sal;
utl_file.put_line(wfile_handle,v_wstring);
end loop;
utl_file.fclose (wfile_handle);
end loop;
end print_reports;
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:
-
fopen opens an OS file and returns a file
handle to Oracle. In the code, the first parameter is the Oracle directory alias (REPORTS), the second is
the file name (stored in v_file) and the third indicates that the file is to be opened in
read and write mode (W).
-
put_line writes a single line to the file and follows it with a newline. In the
code the call in the outer loop writes a header row and the one in the inner loop writes
a data row. The contents of each row (header or data) are made up by a series of concatenated
strings containing column headings (for header rows) and data items (for other rows). Each
column string is separated from the next by a tab which is generated using the Oracle
chr
function (9 being the ASCII code for tab). This ensures that each column is correctly
interpreted by Excel.
-
fclose closes the file.
Closing Remarks:
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