Custom Export to CSV

It's been a while since I've updated my blog. I've been quite busy lately, and just have not had the time that I used to. We're expecting our 1st child in just a few short weeks now, so most of my free time has been spent learning Lamaze breathing, making the weekly run to Babies R Us, and relocating my office from the larger room upstairs to the smaller one downstairs - which I do happen to like MUCH more than I had anticipated. I have everything I need within a short walk - a bathroom, beer fridge, and 52" HD TV. I only need to go upstairs to eat and sleep now, but alas, this will all change soon...

Recently, I was asked if you could change the way Export to CSV in ApEx works. The short answer is, of course, no. But it's not too difficult to "roll your own" CSV export procedure.

Why would you want to do this? Well, the customer's requirement was to manipulate some data when the Export link was clicked, and then export it to CSV in a format which can be imported into PeopleSoft. With the built-in export functionality, this is simply not possible.

It's almost too easy. Simply create a Report Region with your favorite query. Add a button or link or anything which will get you to a blank page. On that page, add a PL/SQL process which will fire "On Load - Before Header" In the source of that process, use this code:

begin
-- Set the MIME type
owa_util.mime_header( 'application/octet', FALSE );
-- Set the name of the file
htp.p('Content-Disposition: attachment; filename="emp.csv"');
-- Close the HTTP Header
owa_util.http_header_close;
-- Loop through all rows in EMP
for x in (select e.ename, e.empno, d.dname
from emp e, dept d where e.deptno = d.deptno
  and e.deptno like :P1_DEPTNO)
loop
 -- Print out a portion of a row,
 -- separated by commas and ended by a CR
 htp.prn(x.ename ||','|| x.empno ||','||
         x.dname || chr(13));
end loop;
-- Send an error code so that the
-- rest of the HTML does not render
htmldb_application.g_unrecoverable_error := true;
end;

You can download the export file here.