Custom CSV Export in APEX

Sometimes the standard export doesn't fit your requirements. For example you do not want the double apostrophe ".



In those cases take a look at these examples:

http://spendolini.blogspot.de/2006/04/custom-export-to-csv.html
http://www.brainre.org/oracle-apex-csv-file-download-with-iso-encoding-not-utf-8/
https://community.oracle.com/thread/2318795

Let us assume that this is our table:

  CREATE TABLE "MY_TABLE" 
   ( "ID" NUMBER, 
 "CAR_NAME" NUMBER, 
 "CAR_VALUE" NUMBER, 
 "CAR_KM" NUMBER
   ) ;

Based on this table I want to create the export.
Next step is to create a view which generates the export as clob.
Why CLOB? The alternative would be a PL/SQL loop which takes much longer to be generated.

CREATE VIEW VW_MY_TABLE AS
SELECT
 XMLCAST(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))) AS CLOB) AS CLOB_VAL,
 COUNT(*) AS NUMBER_OF_ROWS
FROM  (
 SELECT 'ID;CAR_NAME;CAR_VALUE;CAR_KM' AS COL_VALUE FROM DUAL
 UNION ALL
 SELECT ID||';'||
   CAR_NAME||';'||
   CAR_VALUE||';'||
   CAR_KM AS COL_VALUE
 FROM MY_TABLE
);

Finally I create a "On Load - Before Header" process to export the data:

DECLARE
    L_BLOB           BLOB;
    L_CLOB           CLOB;
    L_DEST_OFFSET    INTEGER := 1;
    L_SRC_OFFSET     INTEGER := 1;
    L_LANG_CONTEXT   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING        INTEGER;
    L_LENGTH         INTEGER;
BEGIN

    -- create new temporary BLOB
    DBMS_LOB.CREATETEMPORARY(L_BLOB, FALSE);
    
    --Select CLOB
    SELECT CLOB_VAL INTO L_CLOB FROM VW_MY_TABLE;
    
    -- tranform the input CLOB into a BLOB of the desired charset
    DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB,
                            SRC_CLOB     => L_CLOB,
                            AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                            DEST_OFFSET  => L_DEST_OFFSET,
                            SRC_OFFSET   => L_SRC_OFFSET,
                            BLOB_CSID    => NLS_CHARSET_ID('WE8MSWIN1252'),
                            LANG_CONTEXT => L_LANG_CONTEXT,
                            WARNING      => L_WARNING
                          );

    -- determine length for header
    L_LENGTH := DBMS_LOB.GETLENGTH(L_BLOB);  

    -- create response header
    OWA_UTIL.MIME_HEADER( 'text/csv', FALSE);
    HTP.P('Content-length: ' || L_LENGTH);
    HTP.P('Content-Disposition: attachment; filename="export_my_table.csv"');

    OWA_UTIL.HTTP_HEADER_CLOSE;

    -- download the BLOB
    WPG_DOCLOAD.DOWNLOAD_FILE( L_BLOB );

    -- release BLOB from memory
    DBMS_LOB.FREETEMPORARY(L_BLOB);

    -- stop APEX
    APEX_APPLICATION.STOP_APEX_ENGINE;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_LOB.FREETEMPORARY(L_BLOB);
      RAISE;
END;

Update 14.09.2015
After some problems with Chrome saving the file inline. I updated the script like this:

DECLARE
    L_BLOB           BLOB;
    L_CLOB           CLOB;
    L_DEST_OFFSET    INTEGER := 1;
    L_SRC_OFFSET     INTEGER := 1;
    L_LANG_CONTEXT   INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
    L_WARNING        INTEGER;
    L_LENGTH         INTEGER;
BEGIN

    -- create new temporary BLOB
    DBMS_LOB.CREATETEMPORARY(L_BLOB, FALSE);
    
    --Select CLOB
    SELECT CLOB_VAL INTO L_CLOB FROM VW_MY_TABLE;
    
    -- tranform the input CLOB into a BLOB of the desired charset
    DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB,
                            SRC_CLOB     => L_CLOB,
                            AMOUNT       => DBMS_LOB.LOBMAXSIZE,
                            DEST_OFFSET  => L_DEST_OFFSET,
                            SRC_OFFSET   => L_SRC_OFFSET,
                            BLOB_CSID    => NLS_CHARSET_ID('WE8MSWIN1252'),
                            LANG_CONTEXT => L_LANG_CONTEXT,
                            WARNING      => L_WARNING
                          );

    -- determine length for header
    L_LENGTH := DBMS_LOB.GETLENGTH(L_BLOB);  

    -- first clear the header
    HTP.FLUSH;
    HTP.INIT;

    -- create response header
    OWA_UTIL.MIME_HEADER( 'text/csv', FALSE);

    HTP.P('Content-length: ' || L_LENGTH);
    HTP.P('Content-Disposition: attachment; filename="export_my_table.csv"');
    HTP.P('Set-Cookie: fileDownload=true; path=/');

    OWA_UTIL.HTTP_HEADER_CLOSE;

    -- download the BLOB
    WPG_DOCLOAD.DOWNLOAD_FILE( L_BLOB );

    -- stop APEX
    APEX_APPLICATION.STOP_APEX_ENGINE;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_LOB.FREETEMPORARY(L_BLOB);
      RAISE;
END;

Post Tags:

APEX examples