One thing I periodically need to do in my applications is to have a link which allows the user to download a file. If you’re dealing with a blob column in a report, that’s fairly straightforward; but what if you want to allow the user to export data from your application in a file that’s generated on the fly? There’s nothing in APEX that allows you to say, “I want to have a link/button which takes this query, turns it into XML/JSON/whatever, and downloads it to the client machine”. But it is possible!
Some background may be necessary; feel free to skip this paragraph. A browser following a link doesn’t “know” what it’s going to receive. It makes an HTTP request, and then waits for the HTTP response, which is broken into a header and everything else. The header tells the browser what it’s getting in the rest of the message, and the browser “does the right thing” from there.
In a typical APEX page, the APEX engine generates the HTTP header, telling the browser that it’s getting an HTML document, which is then rendered to the screen for the user. And that’s exactly what we don’t want to have happen if we’re generating a file to be downloaded, so we need to intervene. The simplest way to do this–there are others–is to have a page which consists solely of a “Before Header” PL/SQL process. This means that it fires before the APEX engine generates the HTTP header, so we can generate our own, serve up our file contents, and then shut down the APEX engine so that it doesn’t do anything else on the page. And here’s the code that does just that!
declare l_clob clob; l_blob blob; l_sql_delimiter varchar2(30); l_lang_context integer := DBMS_LOB.DEFAULT_LANG_CTX; l_warning integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR; l_dest_offset integer := 1; l_source_offset integer := 1; begin l_clob := null; dbms_lob.createtemporary(l_blob, true); -- Generate your file content here in l_clob. sys.htp.init; sys.owa_util.mime_header( 'application/octet-stream', FALSE,'UTF-8' ); sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob )); sys.htp.p('Content-Disposition: inline; filename="FILENAME.TXT"' ); sys.owa_util.http_header_close; dbms_lob.converttoblob ( dest_lob => l_blob, src_clob => l_clob, amount => DBMS_LOB.LOBMAXSIZE, dest_offset => l_dest_offset, src_offset => l_source_offset, blob_csid => DBMS_LOB.DEFAULT_CSID, lang_context=> l_lang_context, warning => l_warning ); sys.wpg_docload.download_file(l_blob); apex_application.stop_apex_engine; exception when others then sys.htp.prn('error: '||sqlerrm); apex_application.stop_apex_engine; end;
Trust me, it’s less complicated than it looks. Lines 1-8 are standard variable declarations; lines 10 and 11 initialize the CLOB and BLOB used in the process; line 13 is where you should actually generate your file (or else this code will be fairly boring); lines 15-19 generate the HTTP header we mentioned above (remember to provide a useful filename); lines 21-30 convert the generated CLOB into a BLOB, which is then passed to the browser on line 32; and line 33 just tells the APEX engine to stop. Exactly what we want!
With this in place, you can branch to this page using normal APEX objects, and instead of receiving a new page, the browser will download the file. Useful recipe to have in your toolbox for when you need it.
Edit: Thanks to Dan McGhan (@dmcghan) for pointing out that I referred to the HTML header when I should have properly referred to the HTTP header. HTTP is, of course, the network protocol that browsers generally use, while HTML is simply a type of document. Sorry for any confusion!