Generating CSV files and storing them in the database

This week I faced the following problem: we wanted to generate CSV files based on SQL queries, and store them in the database rather than directly downloading them from a report region. We also wanted a report, from which you could choose which CSV file to download and lastly, the rows in those CSV files could potentially exceed the 32k limit that we currently have with standard report regions.

In Application Express 3.1 we made it really easy to store files in tables with a BLOB column and to build reports on those tables with a download link that allows downloading the files to your client. So this feature takes care of storing and downloading the CSV files. No I only needed to write some logic to actually generate the CSV file and insert it into a table.

The following function does that. You supply a query, then the query is parsed and executed. And then the column headings are derived from the describe table and written to the CSV file, followed by the result set. And in the end, the file is returned back as a BLOB. This BLOB can then be written to a table, or otherwise processed.

<br />create or replace function get_report (<br /> p_query varchar2<br />) return blob is<br /> <br /> l_cursor integer;<br /> l_cursor_status integer;<br /> l_col_count number;<br /> l_desc_tbl sys.dbms_sql.desc_tab2;<br /> l_col_val varchar2(32767);<br /> <br /> l_report blob;<br /> l_raw raw(32767);<br />begin<br /> <br /> -- open BLOB to store CSV file<br /> dbms_lob.createtemporary( l_report, FALSE );<br /> dbms_lob.open( l_report, dbms_lob.lob_readwrite );<br /> <br /> -- parse query<br /> l_cursor := dbms_sql.open_cursor;<br /> dbms_sql.parse(l_cursor, p_query, dbms_sql.native);<br /> dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );<br /><br /> -- define report columns<br /> for i in 1 .. l_col_count loop<br /> dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );<br /> end loop; <br /><br /> -- write column headings to CSV file<br /> for i in 1 .. l_col_count loop<br /> l_col_val := l_desc_tbl(i).col_name;<br /> if i = l_col_count then<br /> l_col_val := '"'||l_col_val||'"'||chr(10); <br /> else<br /> l_col_val := '"'||l_col_val||'",';<br /> end if;<br /> l_raw := utl_raw.cast_to_raw( l_col_val );<br /> dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );<br /> end loop; <br /><br /> l_cursor_status := sys.dbms_sql.execute(l_cursor);<br /><br /> -- write result set to CSV file<br /> loop<br /> exit when dbms_sql.fetch_rows(l_cursor) <= 0; <br /> for i in 1 .. l_col_count loop<br /> dbms_sql.column_value(l_cursor, i, l_col_val);<br /> if i = l_col_count then<br /> l_col_val := '"'||l_col_val||'"'||chr(10); <br /> else<br /> l_col_val := '"'||l_col_val||'",';<br /> end if;<br /> l_raw := utl_raw.cast_to_raw( l_col_val );<br /> dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );<br /> end loop; <br /> end loop;<br /> <br /> dbms_sql.close_cursor(l_cursor);<br /> dbms_lob.close( l_report );<br /> <br /> -- return CSV file<br /> return l_report<br /> <br />end;<br />

To showcase this function, I create an APEX application that has a report page for downloading the CSV files. And a form page that lets you create CSV files based on the demo tables that ship with our APEX sample application. You can try this out here:

http://apex.oracle.com/pls/otn/f?p=36083:1:

logon as demo/demo123

If you’d like to try this on your local APEX instance, review the implementation, and use this for your own application, you can download the packaged application here: CSV and BLOB Demo Application.

This file contains the application along with the supporting objects (get_report function and other required database objects). It does not include the demo tables, so you’ll have to have the APEX default sample application installed in your workspace. If you don’t have this application, click on Create Application -> Create Demonstration Application.

Some other interesting usages for this code could be to generate XML instead of CSV. You would only have to replace the delimiters, etc with XML tags using the column names. This XML file could then be used together with our new PL/SQL Print API, i.e. you could send it off to BI Publisher or Apache FOP, along with a XSL-FO stylesheet or RTF laytout, and produce PDF, Word or Excel files, and either download them to the client or store them in BLOB columns the same way as above. More on this in my next posting.