1

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.

Kevin Jackson
  • 13
  • 3
  • 1
    Perhaps one approach to consider is to add some non-hideable columns to the report. You can set the "Hide" attribute to "No" so the user cannot hide them. Then, add a Condition on them where Request is NULL; this way the columns are never rendered on the web page, but they will appear in any download. – Jeffrey Kemp Aug 20 '19 at 03:55
  • Hi Jeffrey - How would I add the condition _Request is NULL_? – Kevin Jackson Aug 20 '19 at 15:24
  • PL/SQL: `:request is null` – Jeffrey Kemp Aug 20 '19 at 23:53

1 Answers1

2

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

Thomas Tschernich
  • 1,264
  • 15
  • 29