Esport dati da Oracle a Excel – Problema di performance

Sto provando a creare il file xls usando il codice PL / SQL tramite XML. Ho riferito:

Crea file xls usando PL / SQL senza passare attraverso xml

generare file XLS usando PL / SQL

Il codice che ho riferito può essere trovato all'indirizzo: https://akdora.wordpress.com/2009/02/06/how-to-write-excel-via-plsql-and-save-the-file-to-a- directory /

Ma la procedura sta prendendo grande tempo per creare file xls (è stato 160 minuti e la procedura è ancora in esecuzione per esportre 17000 righe e 12 colonne)

Ho bisogno di esportre dati di grandi size (160+ colonne e 400-500 k righe) in formato xls non sarò in grado di utilizzare uno dei pacchetti / pacchetti pagati.

Può chiunque aiutarti a migliorare le performance della procedura.

Codice del pacchetto:

CREATE OR REPLACE PACKAGE pkg_excel_export IS /** * @author : Özay AKDORA * @version : 1.0 * * Name of the Application : pkg_excel_export.sql * Creation/Modification History : 5-Jan-2009 * * Overview of Package/Sample :Create Excel files via PL/SQL * write the file to a directory * **/ PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE worksheet_open ( l_xml_body IN OUT NOCOPY CLOB, p_worksheetname IN VARCHAR2 ); PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB); PROCEDURE cell_write ( l_xml_body IN OUT NOCOPY CLOB, p_content IN VARCHAR2 ); PROCEDURE excel_get ( l_xml_body IN OUT NOCOPY CLOB, p_filename IN VARCHAR2 ); PROCEDURE prc_write_file ( p_filename IN VARCHAR2, p_dir IN VARCHAR2, p_clob IN CLOB ); END pkg_excel_export; / CREATE OR REPLACE PACKAGE BODY pkg_excel_export IS /** * Opens the excel file * **/ PROCEDURE excel_open(l_xml_body IN OUT NOCOPY CLOB) IS BEGIN l_xml_body := '<?xml version="1.0" encoding="ISO-8859-9"?>' || chr(10) || '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"' || chr(10) || 'xmlns:o="urn:schemas-microsoft-com:office:office"' || chr(10) || 'xmlns:x="urn:schemas-microsoft-com:office:excel"' || chr(10) || 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' || chr(10) || 'xmlns:html="http://www.w3.org/TR/REC-html40">' || chr(10) || '<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">' || chr(10) || '<WindowHeight>8580</WindowHeight>' || chr(10) || '<WindowWidth>15180</WindowWidth>' || chr(10) || '<WindowTopX>120</WindowTopX>' || chr(10) || '<WindowTopY>45</WindowTopY>' || chr(10) || '<ProtectStructure>False</ProtectStructure>' || chr(10) || '<ProtectWindows>False</ProtectWindows>' || chr(10) || '</ExcelWorkbook>' || chr(10) || '<Styles>' || chr(10) || '<Style ss:ID="Default" ss:Name="Normal">' || chr(10) || '<Alignment ss:Vertical="Bottom"/>' || chr(10) || '<Borders/>' || chr(10) || '<Font/>' || chr(10) || '<Interior/>' || chr(10) || '<NumberFormat/>' || chr(10) || '<Protection/>' || chr(10) || '</Style>' || chr(10) || '<Style ss:ID="s22">' || chr(10) || '<Font x:Family="Swiss" ss:Bold="1" ss:Underline="Single"/>' || chr(10) || '</Style>' || chr(10) || '</Styles>'; END excel_open; /** * Closes the excel file * **/ PROCEDURE excel_close(l_xml_body IN OUT NOCOPY CLOB) IS BEGIN l_xml_body := l_xml_body || '</Workbook>'; END excel_close; /** * Opens a worksheet in the Excel file. * You may open multiple worksheets. **/ PROCEDURE worksheet_open ( l_xml_body IN OUT NOCOPY CLOB, p_worksheetname IN VARCHAR2 ) IS BEGIN -- -- Create the worksheet -- l_xml_body := l_xml_body || '<Worksheet ss:Name="' || p_worksheetname || '"><Table>'; END worksheet_open; /** * Closes the worksheet in the Excel file. * **/ PROCEDURE worksheet_close(l_xml_body IN OUT NOCOPY CLOB) IS BEGIN l_xml_body := l_xml_body || '</Table></Worksheet>'; END worksheet_close; /** * Opens the row tag * **/ PROCEDURE row_open(l_xml_body IN OUT NOCOPY CLOB) IS BEGIN l_xml_body := l_xml_body || '<Row>'; END row_open; /** * Closes the row tag * **/ PROCEDURE row_close(l_xml_body IN OUT NOCOPY CLOB) IS BEGIN l_xml_body := l_xml_body || '</Row>' || chr(10); END row_close; /** * After opening the row, we can write something the first cell * If you want it blank, write '' **/ PROCEDURE cell_write ( l_xml_body IN OUT NOCOPY CLOB, p_content IN VARCHAR2 ) IS BEGIN l_xml_body := l_xml_body || '<Cell><Data ss:Type="String"> ' || p_content || ' </Data></Cell>'; END cell_write; /** * If you are using this package from APEX, you get download the excel file. * **/ PROCEDURE excel_get ( l_xml_body IN OUT NOCOPY CLOB, p_filename IN VARCHAR2 ) IS xx BLOB; do NUMBER; so NUMBER; bc NUMBER; lc NUMBER; w NUMBER; BEGIN dbms_lob.createtemporary(xx, TRUE); do := 1; so := 1; bc := dbms_lob.default_csid; lc := dbms_lob.default_lang_ctx; w := dbms_lob.no_warning; dbms_lob.converttoblob(xx, l_xml_body, dbms_lob.lobmaxsize, do, so, bc, lc, w); owa_util.mime_header('application/octet', FALSE); -- set the size so the browser knows how much to download htp.p('Content-length: ' || dbms_lob.getlength(xx)); -- the filename will be used by the browser if the users does a save as htp.p('Content-Disposition: attachment; filename="' || p_filename || '.xml' || '"'); -- close the headers owa_util.http_header_close; -- download the BLOB wpg_docload.download_file(xx); END excel_get; /** * Writes the Excel file to some directory with a name. * This procedure writes the CLOB data to file * **/ PROCEDURE prc_write_file ( p_filename IN VARCHAR2, p_dir IN VARCHAR2, p_clob IN CLOB ) IS c_amount CONSTANT BINARY_INTEGER := 32767; l_buffer VARCHAR2(32767); l_chr10 PLS_INTEGER; l_cloblen PLS_INTEGER; l_fhandler utl_file.file_type; l_pos PLS_INTEGER := 1; BEGIN l_cloblen := dbms_lob.getlength(p_clob); l_fhandler := utl_file.fopen(p_dir, p_filename, 'W', c_amount); WHILE l_pos < l_cloblen LOOP l_buffer := dbms_lob.substr(p_clob, c_amount, l_pos); EXIT WHEN l_buffer IS NULL; l_chr10 := instr(l_buffer, chr(10), -1); IF l_chr10 != 0 THEN l_buffer := substr(l_buffer, 1, l_chr10 - 1); END IF; utl_file.put_line(l_fhandler, l_buffer, TRUE); l_pos := l_pos + least(length(l_buffer) + 1, c_amount); END LOOP; utl_file.fclose(l_fhandler); EXCEPTION --WE SHOULD HANDLE THE FILE EXCEPTIONS HERE!!!!! WHEN OTHERS THEN IF utl_file.is_open(l_fhandler) THEN utl_file.fclose(l_fhandler); END IF; RAISE; END; END pkg_excel_export; 

Procedura che richiede tempo:

 CREATE OR REPLACE PROCEDURE SP_STG_BT_GENERATE_XLS_RPT(p_vinput_query IN VARCHAR2, P_report_name IN VARCHAR2, p_user_email IN VARCHAR2, p_module_name IN VARCHAR2, p_vreturncode IN OUT VARCHAR2, p_vreturnmsg IN OUT VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rec_tab DBMS_SQL.desc_tab; col_cnt PLS_INTEGER; dum NUMBER; Select_Statement varchar2(15000); total NUMBER; v_rows_ret NUMBER; v_finalxls VARCHAR2(32767); v_col_val VARCHAR2(32767); v_column_name VARCHAR2(32767); myexcelcontent CLOB; v_sysdate varchar2(50); BEGIN p_vreturncode := 0; p_vreturnmsg := ''; pkg_excel_export.excel_open(myexcelcontent); --open a worksheet pkg_excel_export.worksheet_open(myexcelcontent, 'test'); -- Select_Statement := 'SELECT * FROM t_stg_code where code=''100118'''; Select_Statement := p_vinput_query; Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cur, Select_Statement, DBMS_SQL.NATIVE); dum := DBMS_SQL.EXECUTE(Cur); select dum into total from dual; DBMS_SQL.DESCRIBE_COLUMNS(Cur, col_cnt, rec_tab); pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP /* CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); ELSE DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); END CASE;*/ DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); v_column_name := upper(rec_tab(j).col_name); v_column_name := REPLACE(v_column_name, '"', '&quot;'); v_column_name := REPLACE(v_column_name, '''', '&apos;'); v_column_name := REPLACE(v_column_name, '<', '&lt;'); v_column_name := REPLACE(v_column_name, '>', '&gt;'); v_column_name := REPLACE(v_column_name, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, upper(rec_tab(j).col_name)); END LOOP; pkg_excel_export.row_close(myexcelcontent); -- This part outputs the HEADER -------------------------------------------------------------- LOOP v_rows_ret := DBMS_SQL.FETCH_ROWS(Cur); EXIT WHEN v_rows_ret = 0; v_finalxls := NULL; pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); /* WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_date_val); --v_finalxls := ltrim(v_finalxls||'|'||to_char(v_date_val,'DD/MM/YYYY HH24:MI:SS'),'|'); v_finalxls := ltrim(v_finalxls || '|' || to_char(v_date_val, 'DD/MM/YYYY HH24:MI:SS'), '|'); DBMS_OUTPUT.PUT_LINE(v_col_val);*/ ELSE --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); END CASE; END LOOP; pkg_excel_export.row_close(myexcelcontent); --DBMS_OUTPUT.PUT_LINE(v_finalxls); -- UTL_FILE.PUT_LINE(v_outfile, v_finalxls); END LOOP; pkg_excel_export.worksheet_close(myexcelcontent); --close the file pkg_excel_export.excel_close(myexcelcontent); --get the Time Stamp select to_char(sysdate, 'DDMMYYYYHH24MISS') into v_sysdate from dual; --write the file somewhere pkg_excel_export.prc_write_file(p_filename =>p_module_name||'~'||p_user_email||'~'||P_report_name||'_'||v_sysdate||'.xls', p_dir => 'G:\XLS\', p_clob => myexcelcontent); -- dbms_output.put_line(substr(myexcelcontent, 1, 10000)); END SP_STG_BT_GENERATE_XLS_RPT; END LOOP; CREATE OR REPLACE PROCEDURE SP_STG_BT_GENERATE_XLS_RPT(p_vinput_query IN VARCHAR2, P_report_name IN VARCHAR2, p_user_email IN VARCHAR2, p_module_name IN VARCHAR2, p_vreturncode IN OUT VARCHAR2, p_vreturnmsg IN OUT VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rec_tab DBMS_SQL.desc_tab; col_cnt PLS_INTEGER; dum NUMBER; Select_Statement varchar2(15000); total NUMBER; v_rows_ret NUMBER; v_finalxls VARCHAR2(32767); v_col_val VARCHAR2(32767); v_column_name VARCHAR2(32767); myexcelcontent CLOB; v_sysdate varchar2(50); BEGIN p_vreturncode := 0; p_vreturnmsg := ''; pkg_excel_export.excel_open(myexcelcontent); --open a worksheet pkg_excel_export.worksheet_open(myexcelcontent, 'test'); -- Select_Statement := 'SELECT * FROM t_stg_code where code=''100118'''; Select_Statement := p_vinput_query; Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cur, Select_Statement, DBMS_SQL.NATIVE); dum := DBMS_SQL.EXECUTE(Cur); select dum into total from dual; DBMS_SQL.DESCRIBE_COLUMNS(Cur, col_cnt, rec_tab); pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP /* CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); ELSE DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); END CASE;*/ DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); v_column_name := upper(rec_tab(j).col_name); v_column_name := REPLACE(v_column_name, '"', '&quot;'); v_column_name := REPLACE(v_column_name, '''', '&apos;'); v_column_name := REPLACE(v_column_name, '<', '&lt;'); v_column_name := REPLACE(v_column_name, '>', '&gt;'); v_column_name := REPLACE(v_column_name, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, upper(rec_tab(j).col_name)); END LOOP; pkg_excel_export.row_close(myexcelcontent); -- This part outputs the HEADER -------------------------------------------------------------- LOOP v_rows_ret := DBMS_SQL.FETCH_ROWS(Cur); EXIT WHEN v_rows_ret = 0; v_finalxls := NULL; pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); /* WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_date_val); --v_finalxls := ltrim(v_finalxls||'|'||to_char(v_date_val,'DD/MM/YYYY HH24:MI:SS'),'|'); v_finalxls := ltrim(v_finalxls || '|' || to_char(v_date_val, 'DD/MM/YYYY HH24:MI:SS'), '|'); DBMS_OUTPUT.PUT_LINE(v_col_val);*/ ELSE --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); END CASE; END LOOP; pkg_excel_export.row_close(myexcelcontent); --DBMS_OUTPUT.PUT_LINE(v_finalxls); -- UTL_FILE.PUT_LINE(v_outfile, v_finalxls); END LOOP; pkg_excel_export.worksheet_close(myexcelcontent); --close the file pkg_excel_export.excel_close(myexcelcontent); --get the Time Stamp select to_char(sysdate, 'DDMMYYYYHH24MISS') into v_sysdate from dual; --write the file somewhere pkg_excel_export.prc_write_file(p_filename =>p_module_name||'~'||p_user_email||'~'||P_report_name||'_'||v_sysdate||'.xls', p_dir => 'G:\XLS\', p_clob => myexcelcontent); -- dbms_output.put_line(substr(myexcelcontent, 1, 10000)); END SP_STG_BT_GENERATE_XLS_RPT; CASO FINALE; CREATE OR REPLACE PROCEDURE SP_STG_BT_GENERATE_XLS_RPT(p_vinput_query IN VARCHAR2, P_report_name IN VARCHAR2, p_user_email IN VARCHAR2, p_module_name IN VARCHAR2, p_vreturncode IN OUT VARCHAR2, p_vreturnmsg IN OUT VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rec_tab DBMS_SQL.desc_tab; col_cnt PLS_INTEGER; dum NUMBER; Select_Statement varchar2(15000); total NUMBER; v_rows_ret NUMBER; v_finalxls VARCHAR2(32767); v_col_val VARCHAR2(32767); v_column_name VARCHAR2(32767); myexcelcontent CLOB; v_sysdate varchar2(50); BEGIN p_vreturncode := 0; p_vreturnmsg := ''; pkg_excel_export.excel_open(myexcelcontent); --open a worksheet pkg_excel_export.worksheet_open(myexcelcontent, 'test'); -- Select_Statement := 'SELECT * FROM t_stg_code where code=''100118'''; Select_Statement := p_vinput_query; Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cur, Select_Statement, DBMS_SQL.NATIVE); dum := DBMS_SQL.EXECUTE(Cur); select dum into total from dual; DBMS_SQL.DESCRIBE_COLUMNS(Cur, col_cnt, rec_tab); pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP /* CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); ELSE DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); END CASE;*/ DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); v_column_name := upper(rec_tab(j).col_name); v_column_name := REPLACE(v_column_name, '"', '&quot;'); v_column_name := REPLACE(v_column_name, '''', '&apos;'); v_column_name := REPLACE(v_column_name, '<', '&lt;'); v_column_name := REPLACE(v_column_name, '>', '&gt;'); v_column_name := REPLACE(v_column_name, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, upper(rec_tab(j).col_name)); END LOOP; pkg_excel_export.row_close(myexcelcontent); -- This part outputs the HEADER -------------------------------------------------------------- LOOP v_rows_ret := DBMS_SQL.FETCH_ROWS(Cur); EXIT WHEN v_rows_ret = 0; v_finalxls := NULL; pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); /* WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_date_val); --v_finalxls := ltrim(v_finalxls||'|'||to_char(v_date_val,'DD/MM/YYYY HH24:MI:SS'),'|'); v_finalxls := ltrim(v_finalxls || '|' || to_char(v_date_val, 'DD/MM/YYYY HH24:MI:SS'), '|'); DBMS_OUTPUT.PUT_LINE(v_col_val);*/ ELSE --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); END CASE; END LOOP; pkg_excel_export.row_close(myexcelcontent); --DBMS_OUTPUT.PUT_LINE(v_finalxls); -- UTL_FILE.PUT_LINE(v_outfile, v_finalxls); END LOOP; pkg_excel_export.worksheet_close(myexcelcontent); --close the file pkg_excel_export.excel_close(myexcelcontent); --get the Time Stamp select to_char(sysdate, 'DDMMYYYYHH24MISS') into v_sysdate from dual; --write the file somewhere pkg_excel_export.prc_write_file(p_filename =>p_module_name||'~'||p_user_email||'~'||P_report_name||'_'||v_sysdate||'.xls', p_dir => 'G:\XLS\', p_clob => myexcelcontent); -- dbms_output.put_line(substr(myexcelcontent, 1, 10000)); END SP_STG_BT_GENERATE_XLS_RPT; END LOOP; CREATE OR REPLACE PROCEDURE SP_STG_BT_GENERATE_XLS_RPT(p_vinput_query IN VARCHAR2, P_report_name IN VARCHAR2, p_user_email IN VARCHAR2, p_module_name IN VARCHAR2, p_vreturncode IN OUT VARCHAR2, p_vreturnmsg IN OUT VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rec_tab DBMS_SQL.desc_tab; col_cnt PLS_INTEGER; dum NUMBER; Select_Statement varchar2(15000); total NUMBER; v_rows_ret NUMBER; v_finalxls VARCHAR2(32767); v_col_val VARCHAR2(32767); v_column_name VARCHAR2(32767); myexcelcontent CLOB; v_sysdate varchar2(50); BEGIN p_vreturncode := 0; p_vreturnmsg := ''; pkg_excel_export.excel_open(myexcelcontent); --open a worksheet pkg_excel_export.worksheet_open(myexcelcontent, 'test'); -- Select_Statement := 'SELECT * FROM t_stg_code where code=''100118'''; Select_Statement := p_vinput_query; Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cur, Select_Statement, DBMS_SQL.NATIVE); dum := DBMS_SQL.EXECUTE(Cur); select dum into total from dual; DBMS_SQL.DESCRIBE_COLUMNS(Cur, col_cnt, rec_tab); pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP /* CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); ELSE DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); END CASE;*/ DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); v_column_name := upper(rec_tab(j).col_name); v_column_name := REPLACE(v_column_name, '"', '&quot;'); v_column_name := REPLACE(v_column_name, '''', '&apos;'); v_column_name := REPLACE(v_column_name, '<', '&lt;'); v_column_name := REPLACE(v_column_name, '>', '&gt;'); v_column_name := REPLACE(v_column_name, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, upper(rec_tab(j).col_name)); END LOOP; pkg_excel_export.row_close(myexcelcontent); -- This part outputs the HEADER -------------------------------------------------------------- LOOP v_rows_ret := DBMS_SQL.FETCH_ROWS(Cur); EXIT WHEN v_rows_ret = 0; v_finalxls := NULL; pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); /* WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_date_val); --v_finalxls := ltrim(v_finalxls||'|'||to_char(v_date_val,'DD/MM/YYYY HH24:MI:SS'),'|'); v_finalxls := ltrim(v_finalxls || '|' || to_char(v_date_val, 'DD/MM/YYYY HH24:MI:SS'), '|'); DBMS_OUTPUT.PUT_LINE(v_col_val);*/ ELSE --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); END CASE; END LOOP; pkg_excel_export.row_close(myexcelcontent); --DBMS_OUTPUT.PUT_LINE(v_finalxls); -- UTL_FILE.PUT_LINE(v_outfile, v_finalxls); END LOOP; pkg_excel_export.worksheet_close(myexcelcontent); --close the file pkg_excel_export.excel_close(myexcelcontent); --get the Time Stamp select to_char(sysdate, 'DDMMYYYYHH24MISS') into v_sysdate from dual; --write the file somewhere pkg_excel_export.prc_write_file(p_filename =>p_module_name||'~'||p_user_email||'~'||P_report_name||'_'||v_sysdate||'.xls', p_dir => 'G:\XLS\', p_clob => myexcelcontent); -- dbms_output.put_line(substr(myexcelcontent, 1, 10000)); END SP_STG_BT_GENERATE_XLS_RPT; END LOOP; CREATE OR REPLACE PROCEDURE SP_STG_BT_GENERATE_XLS_RPT(p_vinput_query IN VARCHAR2, P_report_name IN VARCHAR2, p_user_email IN VARCHAR2, p_module_name IN VARCHAR2, p_vreturncode IN OUT VARCHAR2, p_vreturnmsg IN OUT VARCHAR2) IS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR; rec_tab DBMS_SQL.desc_tab; col_cnt PLS_INTEGER; dum NUMBER; Select_Statement varchar2(15000); total NUMBER; v_rows_ret NUMBER; v_finalxls VARCHAR2(32767); v_col_val VARCHAR2(32767); v_column_name VARCHAR2(32767); myexcelcontent CLOB; v_sysdate varchar2(50); BEGIN p_vreturncode := 0; p_vreturnmsg := ''; pkg_excel_export.excel_open(myexcelcontent); --open a worksheet pkg_excel_export.worksheet_open(myexcelcontent, 'test'); -- Select_Statement := 'SELECT * FROM t_stg_code where code=''100118'''; Select_Statement := p_vinput_query; Cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(Cur, Select_Statement, DBMS_SQL.NATIVE); dum := DBMS_SQL.EXECUTE(Cur); select dum into total from dual; DBMS_SQL.DESCRIBE_COLUMNS(Cur, col_cnt, rec_tab); pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP /* CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(Cur, j, 1); ELSE DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); END CASE;*/ DBMS_SQL.DEFINE_COLUMN(Cur, j, 1, 2000); v_column_name := upper(rec_tab(j).col_name); v_column_name := REPLACE(v_column_name, '"', '&quot;'); v_column_name := REPLACE(v_column_name, '''', '&apos;'); v_column_name := REPLACE(v_column_name, '<', '&lt;'); v_column_name := REPLACE(v_column_name, '>', '&gt;'); v_column_name := REPLACE(v_column_name, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, upper(rec_tab(j).col_name)); END LOOP; pkg_excel_export.row_close(myexcelcontent); -- This part outputs the HEADER -------------------------------------------------------------- LOOP v_rows_ret := DBMS_SQL.FETCH_ROWS(Cur); EXIT WHEN v_rows_ret = 0; v_finalxls := NULL; pkg_excel_export.row_open(myexcelcontent); FOR j in 1 .. col_cnt LOOP CASE rec_tab(j).col_type WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_col_val); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); /* WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(Cur, j, v_date_val); --v_finalxls := ltrim(v_finalxls||'|'||to_char(v_date_val,'DD/MM/YYYY HH24:MI:SS'),'|'); v_finalxls := ltrim(v_finalxls || '|' || to_char(v_date_val, 'DD/MM/YYYY HH24:MI:SS'), '|'); DBMS_OUTPUT.PUT_LINE(v_col_val);*/ ELSE --v_finalxls := ltrim(v_finalxls||'|"'||v_col_val||'"','|'); v_finalxls := ltrim(v_finalxls || '|' || v_col_val, '|'); -- DBMS_OUTPUT.PUT_LINE(v_col_val); v_col_val := REPLACE(v_col_val, '"', '&quot;'); v_col_val := REPLACE(v_col_val, '''', '&apos;'); v_col_val := REPLACE(v_col_val, '<', '&lt;'); v_col_val := REPLACE(v_col_val, '>', '&gt;'); v_col_val := REPLACE(v_col_val, '&', '&amp;'); pkg_excel_export.cell_write(myexcelcontent, v_col_val); END CASE; END LOOP; pkg_excel_export.row_close(myexcelcontent); --DBMS_OUTPUT.PUT_LINE(v_finalxls); -- UTL_FILE.PUT_LINE(v_outfile, v_finalxls); END LOOP; pkg_excel_export.worksheet_close(myexcelcontent); --close the file pkg_excel_export.excel_close(myexcelcontent); --get the Time Stamp select to_char(sysdate, 'DDMMYYYYHH24MISS') into v_sysdate from dual; --write the file somewhere pkg_excel_export.prc_write_file(p_filename =>p_module_name||'~'||p_user_email||'~'||P_report_name||'_'||v_sysdate||'.xls', p_dir => 'G:\XLS\', p_clob => myexcelcontent); -- dbms_output.put_line(substr(myexcelcontent, 1, 10000)); END SP_STG_BT_GENERATE_XLS_RPT; 

Abbiamo una funzionalità oracle incorporata o un plugin gratuito che potrebbe essere utilizzato per generare il xls. O se questo codice potrebbe essere ottimizzato per dare risultati più veloci (righe da 100k in <5 minuti)

Generazione di base XML

Questo esempio utilizza la function DBMS_XMLQUERY.GETXML ​​per restituire XML da una query. Questa è una function di cortesia che svolge tutte le azioni necessarie ma è piuttosto inflessibile.

 SET SERVEROUTPUT ON DECLARE v_file UTL_FILE.file_type; v_xml CLOB; v_more BOOLEAN := TRUE; BEGIN -- Create XML document from query. v_xml := DBMS_XMLQUERY.getxml('SELECT table_name, tablespace_name FROM user_tables WHERE rownum & 6'); -- Output XML document to file. v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w'); WHILE v_more LOOP UTL_FILE.put(v_file, Substr(v_xml, 1, 32767)); IF LENGTH(v_xml) > 32767 THEN v_xml := SUBSTR(v_xml, 32768); ELSE v_more := FALSE; END IF; END LOOP; UTL_FILE.fclose(v_file); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255)); UTL_FILE.fclose(v_file); END; / END LOOP; SET SERVEROUTPUT ON DECLARE v_file UTL_FILE.file_type; v_xml CLOB; v_more BOOLEAN := TRUE; BEGIN -- Create XML document from query. v_xml := DBMS_XMLQUERY.getxml('SELECT table_name, tablespace_name FROM user_tables WHERE rownum & 6'); -- Output XML document to file. v_file := UTL_FILE.fopen('C:\Development\XML\', 'test1.xml', 'w'); WHILE v_more LOOP UTL_FILE.put(v_file, Substr(v_xml, 1, 32767)); IF LENGTH(v_xml) > 32767 THEN v_xml := SUBSTR(v_xml, 32768); ELSE v_more := FALSE; END IF; END LOOP; UTL_FILE.fclose(v_file); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(Substr(SQLERRM,1,255)); UTL_FILE.fclose(v_file); END; / 

Ho scritto il pacchetto che hai usato. Aggiungendo le stringhe a CLOB con "||" riduce le performance. Mi sono reso conto che dopo averlo usato con dati enormi come te. Modificare le funzionalità (in particolare PROCEDURA cell_write) con la function di aggiunta di CLOB.

Vedi esempio come utilizzarlo: Clob Append