Custom CSV Export as ZIP file

At the beginning of this year I wrote an article about "Custom CSV Export in APEX".

Now I want to extend the solution by an optional ZIP export.

Why?
You can not export several files at the same time with standard APEX features. For that you have to create a ZIP file including all the files you want to download.

I made an example application "Multi CSV Download as ZIP file" where you see a report including all employee columns and the department name. Above the report is a select list where you can filter for departments.

When you select a department and click on EXPORT then you get an CSV file including all employees for the selected department. In case you don't filter you get an ZIP file including a CSV file for each department.

To create the ZIP file I used the AS_ZIP Package created by Anton Scheffer.

Here is code I used to create the CSV/ZIP file.

DECLARE
    -- Blob Conversion Parameter
    L_BLOB           BLOB;
    L_BLOB_FINAL     BLOB;
    L_NAME           VARCHAR2(200); 
    L_NAME_DOWNLOAD  VARCHAR2(200); 
    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;
    
    
    -- CSV Select
    CURSOR c1 IS
      SELECT
       DNAME,
       '"EMPNO"¡"ENAME"¡"JOB"¡"MGR"¡"HIREDATE"¡"SAL"¡"COMM"' 
         || CHR(13)|| CHR(10) 
         || XMLCAST(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))) AS CLOB) AS CLOB_VAL
      FROM  (
            SELECT
              DNAME,
              '"'||EMPNO||'"¡"'||ENAME||'"¡"'||JOB||'"¡"'||MGR||'"¡"'||HIREDATE||'"¡"'||SAL||'"¡"'||COMM||'"' as COL_VALUE
            FROM EMP E
            JOIN DEPT D 
            ON (E.DEPTNO = D.DEPTNO)
            WHERE :P1_DEPT IS NULL
            OR    D.DEPTNO = :P1_DNAME
      ) 
      GROUP BY 
        DNAME
      ;
BEGIN
    if :P1_DNAME is null then
   
      for rec in c1 loop    
        DBMS_LOB.createtemporary(L_BLOB, FALSE);
        L_DEST_OFFSET    := 1;
        L_SRC_OFFSET     := 1;
        L_LANG_CONTEXT   := DBMS_LOB.DEFAULT_LANG_CTX;
        L_WARNING        := null;
      
        -- tranform the input CLOB into a BLOB of the desired charset
        DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB,
                                SRC_CLOB     => rec.CLOB_VAL,
                                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
                              );
    
         L_NAME := 'EXPORT_' || trim(to_char(rec.DNAME)) || '.csv';
    
         AS_ZIP.add1file (  P_ZIPPED_BLOB => L_BLOB_FINAL,
                  P_NAME => L_NAME,
                  P_CONTENT => L_BLOB
                  ) ;  
    
         -- dbms_lob.freetemporary(L_BLOB);
      end loop; 
  
      L_NAME_DOWNLOAD := 'EXPORT_ALL.zip';
      AS_ZIP.finish_zip (  P_ZIPPED_BLOB => L_BLOB_FINAL) ;  
    
    else
      for rec in c1 loop    
        DBMS_LOB.createtemporary(L_BLOB_FINAL, FALSE);
      
        -- tranform the input CLOB into a BLOB of the desired charset
        DBMS_LOB.CONVERTTOBLOB( DEST_LOB     => L_BLOB_FINAL,
                                SRC_CLOB     => rec.CLOB_VAL,
                                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
                              );
    
         L_NAME_DOWNLOAD := 'EXPORT_' || trim(to_char(rec.DNAME)) || '.csv';
    
         exit;
      end loop; 
    end if;

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

    -- first clear the header
    htp.flush;
    htp.init;

    -- create response header
    OWA_UTIL.MIME_HEADER( 'application/zip', FALSE);
    htp.p('Content-length: ' || L_LENGTH);
    htp.p('Content-Disposition: attachment; filename="'||L_NAME_DOWNLOAD||'"');
    htp.p('Set-Cookie: fileDownload=true; path=/');

    OWA_UTIL.HTTP_HEADER_CLOSE;
    -- download the BLOB
    WPG_DOCLOAD.DOWNLOAD_FILE( L_BLOB_FINAL );

    -- stop APEX
    APEX_APPLICATION.STOP_APEX_ENGINE;
  EXCEPTION
    WHEN OTHERS THEN
      if :P1_DNAME is null then
        DBMS_LOB.FREETEMPORARY(L_BLOB);
      end if;
      DBMS_LOB.FREETEMPORARY(L_BLOB_FINAL);
      RAISE;
END;

Updated 27.08.2018:
Old logic: DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))).EXTRACT('//text()').GETCLOBVAL(),1) AS CLOB_VAL
New logic since 12.1: XMLCAST(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))) AS CLOB) AS CLOB_VAL

Post Tags:

APEX examples SQL