Oracle APEX – Download hidden SQL query into CSV

I am trying to create a button on a page in my application that will download the full table I am referencing as a CSV file. I cannot use interactive reports > actions > download CSV because the interactive reports have hidden columns. I need all columns to populate in the CSV file.

Is there a way to create a SQL Script and reference it in the button?

I have already tried the steps referenced in this link: Oracle APEX – Export a query into CSV using a button but it does not help as my queries will contain columns that are hidden in the Interactive Report.

Answer

Welcome to StackOverflow!

One flexible option would be to use an application process, to be defined in the shared components (process point = ajax callback). Something like this:

declare 
    lClob clob;
    lBlob blob;
    lFilename varchar2(250) := 'filename.csv';
begin
    lClob := UNISTR('FEFF'); -- was necessary for us to be able to use the files in MS Excel
    lClob := lClob || 'Tablespace Name;Table Name;Number of Rows' || utl_tcp.CRLF;

    for c in (select tablespace_name, table_name, num_rows from user_tables where rownum <= 5)
    loop
        lClob := lClob || c.tablespace_name || ';' || c.table_name || ';' || c.num_rows || utl_tcp.CRLF;
    end loop;

    lBlob := fClobToBlob(lClob);

    sys.htp.init;
    sys.owa_util.mime_header('text/csv', false);
    sys.htp.p('Conent-length: ' || dbms_lob.getlength(lBlob));
    sys.htp.p('Content-Disposition: attachment; filename = "' || lFilename || '"');
    sys.htp.p('Cache-Control: no-cache, no-store, must-revalidate');
    sys.htp.p('Pragma: no-cache');
    sys.htp.p('Expires: 0');
    sys.owa_util.http_header_close;
    sys.wpg_docload.download_file(lBlob);    

end;

This is the function fClobToBlob:

create function fClobToBlob(aClob CLOB) RETURN BLOB IS
    tgt_blob BLOB;
    amount INTEGER := DBMS_LOB.lobmaxsize;
    dest_offset INTEGER := 1;
    src_offset INTEGER  := 1;
    blob_csid INTEGER := nls_charset_id('UTF8');
    lang_context INTEGER := DBMS_LOB.default_lang_ctx;
    warning INTEGER := 0;
begin
    if aClob is null then
        return null;
    end if;

    DBMS_LOB.CreateTemporary(tgt_blob, true);
    DBMS_LOB.ConvertToBlob(tgt_blob, aClob, amount, dest_offset, src_offset, blob_csid, lang_context, warning);
    return tgt_blob;
end fClobToBlob;

On the page, you need to set your button action to “Redirect to Page in this Application”, the target Page to “0”. Under “Advanced”, set Request to “APPLICATION_PROCESS=downloadCSV”, where downloadCSV is the name of your application process.

If you need to parameterize your process, you can do this by accessing page items or application items in your application process.

Generating the CSV data can be cumbersome, but there are several packages out there that make it easier. The alexandria packages are one of them: https://github.com/mortenbra/alexandria-plsql-utils

An example on how to use the CSV Package is here: https://github.com/mortenbra/alexandria-plsql-utils/blob/master/demos/csv_util_pkg_demo.sql