Extending the simple MS Excel procedure to include headers

Oracle 10g

MS Excel

In my previous post titled "Simplest example of writing to MS Excel using an Oracle Stored Procedure", I demonstrated what I believe to be the simplest way of outputting a file that can be used by MS Excel. But the example does not include the column headings.... Okay, thats simple enough. In this post, I will extend on the previous example to include the headings.  So without further ado....

In the previous post, I created a stored procedure called ALL_OBJECTS_TO_CSV. This stored procedure was responsible for running a select statement on the ALL_OBJECTS view and to write the results as a CSV file to a share directory.

The stored procedure uses the DBMS_SQL package to execute the select statement and return the results. This can also help us in dynamically querying the column definitions that can be used to generate the column headers for the CSV file.

Line 43 of the stored procedure reads the column definitions from the cursor into the L_COL_DESC variable.

dbms_sql.describe_columns2(l_cur, l_col_cnt, l_col_desc);

Each of the column definitions contain the column names. So a simple loop over the column definitions can write the column headers to the file. I inserted the following additional code at line 45 to output the column headers:

45          -- Output column headers
46          l_buf := '';
47          for i in 1..l_col_cnt loop
48
49                  l_buf := l_buf || '"'
50                                 || replace(l_col_desc(i).col_name, '"', '""'

51                                 || '"' || l_sep;
52
53          end loop;
54          l_buf := rtrim(l_buf, l_sep);
55          utl_file.put_line(l_hnd, l_buf);