Custom CSV Export in APEX

Von Tobias Arnhold 2.10.2015

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
 DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))).EXTRACT('//text()').GETCLOBVAL(),1) 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:

1 One Comment " Custom CSV Export in APEX "

Jorge Rimblas 10 February, 2015 16:05

I think that STOP_APEX_ENGINE works by raising an exception. As such, it should be the last line of code. I don't think the freetemporary on line 45 would ever execute.