Easy File Downloads with APEX_DATA_EXPORT.DOWNLOAD

Need to download a file from an APEX page but not sure how?

Before you write your own custom PL/SQL procedure that requires setting HTTP headers and uses sys.WPG_DOCLOAD.download_file or HTP.PRN to output content, consider using APEX_DATA_EXPORT.DOWNLOAD instead.

The syntax for this procedure is straightforward and only requires passing in an object of apex_data_export.t_export type.
PROCEDURE DOWNLOAD ( p_export IN OUT NOCOPY t_export, p_content_disposition IN t_content_disposition DEFAULT c_attachment, p_stop_apex_engine IN BOOLEAN DEFAULT TRUE );

Creating this export record is easy if you know how to open an APEX_EXEC context and pass it to APEX_DATA_EXPORT.EXPORT to generate the t_export object.

DECLARE
    l_context apex_exec.t_context; 
    l_export  apex_data_export.t_export;
BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from emp' );

    l_export := apex_data_export.export (
                        p_context   => l_context,
                        p_format    => apex_data_export.c_format_pdf );

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;

However, if you already have the BLOB or CLOB and don't want to be limited to the formats supported by APEX_DATA_EXPORT, you can also just create the t_export object manually and assign your own BLOB or CLOB values to it.

--From the APEX_DATA_EXPORT package:
type t_export is record (
    file_name               varchar2(32767),
    format                  t_format,
    mime_type               varchar2(32767),
    as_clob                 boolean,
    content_blob            blob,
    content_clob            clob );

The only record variables you need to populate are file_name, mime_type, and either content_blob or content_clob. If you're using content_clob then you also need to make sure as_clob is set to true since this defaults to false.

The simplest way to do this is to add the following code to a "Before Header" process on the page you need:

declare
   l_export apex_data_export.t_export := apex_data_export.t_export(
      file_name => 'some.txt'
    , mime_type => 'text/plain'
    , content_blob => utl_raw.cast_to_raw('hello world!')
   );
begin
   apex_data_export.download(
      p_export => l_export
   );
end;

This is such a common task that I recommend creating a couple of wrapper procedures that are overloaded to handle either a BLOB or CLOB.

Checkout the export_util package in this Gist for examples: