Data Management
Project Management
Archived stuff
Cryptic crossword.
A bit about me.
Read my blog
Recent additions
Home
Oracle Articles

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.

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.

On execution, the procedure creates 6 files in the directory REPORTS - one per manager who has direct reports (BLAKE, CLARK, FORD, JONES, KING and SCOTT). The filenames have the format <NAME>_direct_reports_<DATE>.xls.

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