prog:plsql_spool_csv_apex
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende Überarbeitung | |||
prog:plsql_spool_csv_apex [2016/10/31 13:34] – gpipperr | prog:plsql_spool_csv_apex [2016/10/31 13:34] (aktuell) – [Quellen] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====Oracle PL/SQL - APEX - CSV Datei aus der DB mit DBMS_XMLGEN.CONVERT erzeugen ===== | ||
+ | Unter http:// | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | Hier das ganz auf größere Dateien umgebaut: | ||
+ | <code plsql> | ||
+ | declare | ||
+ | -- ============================================ | ||
+ | -- First version => thanks to from http:// | ||
+ | -- read the parameter as clob | ||
+ | -- ============================================ | ||
+ | v_blob blob; | ||
+ | v_clob clob; | ||
+ | v_tmp_clob clob; | ||
+ | v_dest_offset | ||
+ | v_src_coffset | ||
+ | v_lang_context integer := dbms_lob.default_lang_ctx; | ||
+ | v_warning | ||
+ | v_length | ||
+ | v_task | ||
+ | v_row_count pls_integer :=0; | ||
+ | v_chunk_size pls_integer: | ||
+ | v_reads pls_integer | ||
+ | v_offset pls_integer | ||
+ | v_debug_loop_counter | ||
+ | | ||
+ | begin | ||
+ | |||
+ | v_task: | ||
+ | -- create new temporary blob | ||
+ | dbms_lob.createtemporary(v_blob, | ||
+ | |||
+ | begin | ||
+ | | ||
+ | | ||
+ | | ||
+ | --Select CLOB | ||
+ | if v_task=' | ||
+ | |||
+ | select count(*) into v_row_count from crr_admin.t_param_i_backup; | ||
+ | |||
+ | -- how often we have to loop over the data | ||
+ | v_reads: | ||
+ | for i in 1 .. v_reads | ||
+ | loop | ||
+ | | ||
+ | | ||
+ | | ||
+ | SELECT PARAM_TABLE_NAME||';' | ||
+ | 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: | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | end loop; | ||
+ | |||
+ | end if; | ||
+ | |||
+ | exception | ||
+ | when others then | ||
+ | raise_application_error(-20001 , '-- Error read DATA at::' | ||
+ | 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(' | ||
+ | , lang_context => v_lang_context | ||
+ | , warning => v_warning ); | ||
+ | |||
+ | exception | ||
+ | when others then | ||
+ | raise_application_error(-20001 , '-- Error create BLOB ::' | ||
+ | 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( ' | ||
+ | |||
+ | htp.p(' | ||
+ | htp.p(' | ||
+ | htp.p(' | ||
+ | |||
+ | 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(' | ||
+ | , 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 " | ||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | * http:// |
prog/plsql_spool_csv_apex.txt · Zuletzt geändert: 2016/10/31 13:34 von gpipperr