Simplest example of writing to MS Excel using an Oracle Stored Procedure

Oracle 10g
MS Excel

First of all, we need to define exactly what is meant by "writing to Excel worksheet". Most people just mean produce something that MS Excel can show as a worksheet. A CSV file is the simplest file that can be displayed by MS Excel and since it is a simple text markup, is the easiest to produce via PL/SQL.

Generating the file using a stored procedure is seriously simple. But you will need to also give thought to how you will make the file available to excel (how to distribute the file). In my example, I make the file available on a network share. The user will find the file available there and open it using MS Excel.

The first thing we need is a directory that is accessible to both the user and the Oracle database. I chose C:\TEMP\ on the server and shared it so that I can access it remotely.

We then need to define a directory object in the Oracle database and assign permissions. This will make the directory available to our stored procedure to be able to write to it. To do this, we must use the CREATE DIRECTORY command. Take note that you need CREATE ANY DIRECTORY privilege in order to run this command. Typically, only DBA will have this permission.

Once the directory is created by the DBA, the read/write permissions must be granted to the Oracle user that will own the procedure that will generate the file. In my case, that user is CSVUSER.

dayneo@SANDBOX> create directory TEMP_DIR  AS 'C:\Temp'
  2  /

Directory created.

dayneo@SANDBOX>
dayneo@SANDBOX> grant read, write on directory TEMP_DIR to csvuser
  2  /

Grant succeeded.

dayneo@SANDBOX>

Now we need the oracle stored procedure that generates the file. I will keep it simple and select two columns from the ALL_OBJECTS view. We will use UTL_FILE and DBMS_SQL to produce a semi-generic CSV generator procedure (you will have to change the query and column definitions). The procedure will accept a parameter as a filter to the query and another for the name of the file I want generated. 

csvuser@SANDBOX> create or replace procedure allobjects_to_csv(p_filename in varchar2,
  2           p_object_type in varchar2 default 'TABLE') as
  3  
  4        VARCHAR2_COLTYPE constant pls_integer := 1;
  5        NUMBER_COLTYPE   constant pls_integer := 2;
  6        DATE_COLTYPE     constant pls_integer := 12;
  7        CTRLF       constant varchar2(2) := chr(10) || chr(13);
  8        WRITE_TEXT       constant varchar2(1) := 'w';
  9  
 10        l_cur number;
 11        l_qry varchar2(32767);
 12        l_chr varchar2(32767);
 13        l_n number;
 14  
 15        l_col_cnt pls_integer;
 16        l_col_desc dbms_sql.desc_tab2;
 17        l_vchar varchar2(32767);
 18        l_vnum number;
 19        l_vdate date;
 20        l_buf varchar2(32767);
 21        l_sep varchar2(1) := ','; -- the separater
 22  
 23        l_hnd utl_file.file_type;
 24  
 25  begin
 26  
 27        -- open file for writing
 28        l_hnd := utl_file.fopen('TEMP_DIR', p_filename, WRITE_TEXT, 32767);
 29  
 30        -- #1. Query
 31        l_qry := 'select owner || ''.'' || object_name object_name, object_type
 32  from all_objects
 33  where object_type like :bv
 34  order by object_name';
 35        l_cur := dbms_sql.open_cursor();
 36        dbms_sql.parse(l_cur, l_qry, dbms_sql.NATIVE);
 37        dbms_sql.define_column(l_cur,  1, l_chr, 61); -- TABLE_NAME
 38        dbms_sql.define_column(l_cur,  2, l_chr, 19); -- TABLE_TYPE
 39        dbms_sql.bind_variable(l_cur, ':bv', p_object_type);
 40        l_n := DBMS_SQL.EXECUTE(l_cur);
 41  
 42        -- Describe the resultset
 43        dbms_sql.describe_columns2(l_cur, l_col_cnt, l_col_desc);
 44  
 45        -- for each row
 46        while dbms_sql.fetch_rows(l_cur) > 0 loop
 47  
 48         l_buf := '';
 49  
 50         -- for each column
 51         for i in 1..l_col_cnt loop
 52  
 53           -- TODO: Find all the various datatypes and handle them here
 54          case l_col_desc(i).col_type
 55           when VARCHAR2_COLTYPE then
 56  
 57            -- quotes in string values must be double quoted.
 58            -- string values should be quoted.
 59            dbms_sql.column_value(l_cur, i, l_vchar);
 60            l_buf := l_buf || '"' || replace(l_vchar, '"', '""') || '"' || l_sep;
 61  
 62           when NUMBER_COLTYPE then
 63  
 64            dbms_sql.column_value(l_cur, i, l_vnum);
 65            -- you need to use the to_char to make sure you don't get
 66            -- an implicit conversion from num to char using NLS_ params
 67            -- NLS might define number with , separators which would break
 68            -- the csv format.
 69            l_buf := l_buf || to_char(l_vnum, '999999999990.0') || l_sep;
 70  
 71           when DATE_COLTYPE then
 72  
 73            dbms_sql.column_value(l_cur, i, l_vdate);
 74            -- you need to use the to_char to make sure you don't get
 75            -- an implicit conversion from date to char using NLS_ params
 76            -- NLS might define date with , separators which would break
 77            -- the csv format.
 78            l_buf := l_buf || to_char(l_vdate, 'DD MON YYYY HH24:MI:SS' ) || l_sep;
 79  
 80           else
 81  
 82            raise_application_error(-20000,
 83               'Unknown column type found for column "'
 84                    || l_col_desc(i).col_name
 85                    || '"');
 86  
 87           end case;
 88  
 89          end loop;
 90  
 91          -- remove the trailing separator
 92          l_buf := rtrim(l_buf, l_sep);
 93  
 94          -- write line to file
 95          utl_file.put_line(l_hnd, l_buf);
 96  
 97         end loop;
 98  
 99        utl_file.fclose(l_hnd);
100        dbms_sql.close_cursor(l_cur);
101  
102  exception
103        when OTHERS then
104         if utl_file.is_open(l_hnd) then
105          utl_file.fclose(l_hnd);
106         end if;
107         if dbms_sql.is_open(l_cur) then
108          dbms_sql.close_cursor(l_cur);
109         end if;
110         raise;
111  end allobjects_to_csv;
112  /

Procedure created.

csvuser@SANDBOX> 
csvuser@SANDBOX> SHOW ERRORS
No errors.
csvuser@SANDBOX> 

Now we have a stored procedure that will output two columns of the ALL_OBJECTS view to a file on the C:\Temp directory. After running the procedure, I will find a file in the shared directory. Users can open the file using MS Excel.

csvuser@SANDBOX> begin
  2
  3          allobjects_to_csv('myfile.csv');
  4
  5  end;
  6  /

PL/SQL procedure successfully completed.

csvuser@SANDBOX>

The file is now available on the share directory we set up in the beginning. The file can be opened using a text editor such as Notepad to view the actual format that is output to the file. If you have MS Excel installed, then it will open the file by default.

There are many ways of distributing the file instead of writing to a share directory. You could email the file, you could make available via web, or you could make it downloadable by some custom client side application. All of these options are available through PL/SQL.