Monday, March 15, 2010

Output in MS Excel-format for browser in PL/SQL

This example will show how to output Tab-Separated-Values with Oracle PL/SQL for Microsoft Excel in a browser. This is not exactly JEE, but the main idea can also be applied in Java.

Create a public accessible procedure that outputs Tab-Separated-Values. Make sure the execute rights are granted on user 'PUBLIC' for the procedure, otherwise the procedure is not allowed to be called by public users.

procedure example is
cursor c_cursor is
select col1, col2
from table;

r_row c_cursor%rowtype;
begin
-- Print TSV header.
owa_util.mime_header('application/vnd.ms-excel');

htp.print('COL1' || CHR(9) ||
'COL2' || CHR(9) ||
CHR(13));

open c_cursor;
loop
fetch c_cursor into r_row;
exit when c_cursor%NOTFOUND;

-- Print TSV row.
htp.print(r_row.col1 || CHR(9) ||
r_row.col2 || CHR(9) ||
CHR(13));
end loop;
close c_cursor;
end example;

The main point in this procedure is to let the browser know we are dealing with output intended for Microsoft Excel. We use this procedure to set the MIME-type in the HTTP-header of the response:

owa_util.mime_header('application/vnd.ms-excel');

The browser will then forward the output to MS Excel for processing.

In the procedure we output the values separated by tabs, which are generated with the PL/SQL function CHR(9). The line is then closed with a new-line using CHR(13).

The output is accessible using the URL structure:

http://<hostname>/pls/portal/<schema>.<package>.<procedure>

No comments:

Post a Comment