prog:plsql_spool_csv

Oracle PL/SQL - APEX - CSV Datei aus der DB mit DBMS_XMLGEN.CONVERT erzeugen

Unter http://www.apex-at-work.com/2015/02/custom-csv-export-in-apex.html habe ich eine sehr gute Idee für das einfache Erzeugen eines CSV Exports gefunden.

Hier das ganz auf größere Dateien umgebaut:

DECLARE
  -- ============================================
  -- First version => thanks to from http://www.apex-at-work.com/2015/02/custom-csv-export-in-apex.html
  -- read the parameter as clob
  -- ============================================
  v_blob blob;
  v_clob clob;
  v_tmp_clob clob;
  v_dest_offset  INTEGER := 1;
  v_src_coffset  INTEGER := 1;
  v_lang_context INTEGER := DBMS_LOB.default_lang_ctx;
  v_warning      INTEGER;
  v_length       INTEGER;
  v_task         VARCHAR2(200);
  v_row_count PLS_INTEGER :=0;
  v_chunk_size PLS_INTEGER:=200000;
  v_reads PLS_INTEGER     :=0;
  v_offset PLS_INTEGER    :=0;
  v_debug_loop_counter   PLS_INTEGER:=0;
 
BEGIN
 
  v_task:=apex_util.get_session_state('P26_EXPORT_TASK');
  -- create new temporary blob
  DBMS_LOB.createtemporary(v_blob, FALSE);
 
  BEGIN
 
   v_clob:='PARAM_TABLE_NAME;PARAM_01;PARAM_02;PARAM_03;PARAM_04;PARAM_05;PARAM_06;PARAM_07;PARAM_08;PARAM_09;PARAM_10;PARAM_11;PARAM_12;PARAM_13;PARAM_14;PARAM_15;PARAM_16;PARAM_17;PARAM_18;PARAM_19;PARAM_20;PARAM_21;PARAM_22;PARAM_23;PARAM_24;PARAM_25;PARAM_26;PARAM_27;PARAM_28;PARAM_29;PARAM_30'||pkg_admin_props.G_CTRL;
 
    --Select CLOB
    IF v_task='I_TABLE' THEN
 
      SELECT COUNT(*) INTO v_row_count FROM crr_admin.t_param_i_backup;
 
      -- how often we have to loop over the data
      v_reads:=CEIL(v_row_count/v_chunk_size);
      FOR i  IN 1 .. v_reads
      LOOP
 
         SELECT  DBMS_XMLGEN.CONVERT(XMLAGG(XMLELEMENT(E,COL_VALUE||CHR(13)||CHR(10))).EXTRACT('//text()').GETCLOBVAL(),1) INTO v_tmp_clob
           FROM  (
              SELECT PARAM_TABLE_NAME||';'||PARAM_01||';'||PARAM_02||';'||PARAM_03||';'||PARAM_04||';'||PARAM_05||';'||PARAM_06||';'||PARAM_07||';'||PARAM_08||';'||PARAM_09||';'||PARAM_10||';'||PARAM_11||';'||PARAM_12||';'||PARAM_13||';'||PARAM_14||';'||PARAM_15||';'||PARAM_16||';'||PARAM_17||';'||PARAM_18||';'||PARAM_19||';'||PARAM_20||';'||PARAM_21||';'||PARAM_22||';'||PARAM_23||';'||PARAM_24||';'||PARAM_25||';'||PARAM_26||';'||PARAM_27||';'||PARAM_28||';'||PARAM_29||';'||PARAM_30
             AS COL_VALUE
             FROM crr_admin.T_PARAM_I_BACKUP
             ORDER BY 1 DESC OFFSET v_offset ROWS FETCH NEXT v_chunk_size ROWS ONLY
            );
 
        v_offset:=v_offset+v_chunk_size ;
 
       v_clob:=v_clob||v_tmp_clob;
       v_debug_loop_counter:=i;
 
      END LOOP;
 
    END IF;
 
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001 , '-- Error read DATA at::'||$$plsql_unit||'v_row_count:='||v_row_count||' v_offset:='||v_offset||' v_reads::'||v_reads||' v_debug_loop_counter'||v_debug_loop_counter||' Error::'||SQLERRM);
  END;
 
  BEGIN
    -- tranform the input CLOB into a BLOB of the desired charset
    DBMS_LOB.converttoblob( dest_lob => v_blob
                            , src_clob => v_clob
                           , amount => DBMS_LOB.lobmaxsize
                           , dest_offset => v_dest_offset
                           , src_offset => v_src_coffset
                           , blob_csid => NLS_CHARSET_ID('WE8MSWIN1252') --nls_charset_id('AL16UTF16')
                           , lang_context => v_lang_context
                           , warning => v_warning );
 
  EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20001 , '-- Error create BLOB ::'||$$plsql_unit||' :: '||SQLERRM);
  END;
 
  -- determine length for header
  v_length := DBMS_LOB.getlength(v_blob);
 
  -- first clear the header
  htp.flush;
  htp.init;
 
  -- create response header
  owa_util.mime_header( 'text/csv', FALSE, 'ISO-8859-1' ); --application/octet-stream -- UTF8
 
  htp.p('Content-length: ' || v_length);
  htp.p('Content-Disposition: attachment; filename="CRR_parameter_export.csv"');
  htp.p('Set-Cookie: fileDownload=true; path=/');
 
  owa_util.http_header_close;
 
  -- download the BLOB
  wpg_docload.download_file( v_blob );
 
 
  INSERT
  INTO crr_admin.t_p_import_historie
    (
      id
    , import_task
    , import_user
    , import_date
    )
    VALUES
    (
      t_p_import_historie_seq.NEXTVAL
    , pkg_admin_props.g_export_csv_param
    , NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'),SYS_CONTEXT('USERENV','PROXY_USER'))
    , SYSDATE
    );
 
  COMMIT;
 
  -- stop APEX
  apex_application.stop_apex_engine;
 
EXCEPTION
WHEN OTHERS THEN
  DBMS_LOB.freetemporary(v_blob);
  RAISE;
END;

Das ganze wird in der „Pre-Rendering/Before Header“ auf der Apex Page hinterlegt und damit als erstes in der Seite aufgerufen.

Quellen

http://www.apex-at-work.com/2015/02/custom-csv-export-in-apex.html