prog:plsql_spool_csv_apex
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
prog/plsql_spool_csv_apex.txt · Zuletzt geändert: 2016/10/31 13:34 von gpipperr