Sunday, April 20, 2008

APEX Advanced Training in Munich, Germany (June 2-4)




I'm happy to announce that I've been invited to speak at the APEX Advanced Training in Munich, Germany, June 2nd to June 4th. This is a three day training class held by Denes Kubicek, Patrick Wolf and Dietmar Aust. All three of them are well know APEX developers and very active participants in the APEX community and so I was particularly pleased to accept their invitation to talk about what we're working on for our next release of Oracle Application Express and to demonstrate our newest release of Oracle SQL Developer.


I'll also be participating in the two evening Q&A sessions, helping to answer customer questions and hoping to learn a few things about the experience our customers have with APEX and hearing about their ideas and need for future versions.


Please visit the training web site Opal Consulting for more information on this event.


Friday, April 18, 2008

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.



create or replace function get_report (
p_query varchar2
) return blob is

l_cursor integer;
l_cursor_status integer;
l_col_count number;
l_desc_tbl sys.dbms_sql.desc_tab2;
l_col_val varchar2(32767);

l_report blob;
l_raw raw(32767);
begin

-- open BLOB to store CSV file
dbms_lob.createtemporary( l_report, FALSE );
dbms_lob.open( l_report, dbms_lob.lob_readwrite );

-- parse query
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, p_query, dbms_sql.native);
dbms_sql.describe_columns2(l_cursor, l_col_count, l_desc_tbl );

-- define report columns
for i in 1 .. l_col_count loop
dbms_sql.define_column(l_cursor, i, l_col_val, 32767 );
end loop;

-- write column headings to CSV file
for i in 1 .. l_col_count loop
l_col_val := l_desc_tbl(i).col_name;
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'",';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );
end loop;

l_cursor_status := sys.dbms_sql.execute(l_cursor);

-- write result set to CSV file
loop
exit when dbms_sql.fetch_rows(l_cursor) <= 0;
for i in 1 .. l_col_count loop
dbms_sql.column_value(l_cursor, i, l_col_val);
if i = l_col_count then
l_col_val := '"'||l_col_val||'"'||chr(10);
else
l_col_val := '"'||l_col_val||'",';
end if;
l_raw := utl_raw.cast_to_raw( l_col_val );
dbms_lob.writeappend( l_report, utl_raw.length( l_raw ), l_raw );
end loop;
end loop;

dbms_sql.close_cursor(l_cursor);
dbms_lob.close( l_report );

-- return CSV file
return l_report

end;


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.