=====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:
<code plsql>
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;
</code>


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