Hello everybody.
I've been trying to use a routine in pl/sql for two days to download files in several parts.
The scenario is this: clicking on a button will call a procedure that will have a loop to fetch data in a table, I need that when I pass an amount of data, the file is downloaded and the process continues until the end.
Example: loop returns 35 lines, I want every 10 to download and continue, that way I would have 4 file at the end. Three with 10 and the last one with 4 record lines. Here's the routine I'm using:
for st_main in (
select field1, field2, field3, ...
from table_dados where filters = filters
) loop
wCont := 0;
dbms_lob.createtemporary(l_clob, FALSE);
for st_downlod(
select field1, field2, field3, ...
from table_dados where filters = st_main.filters
) loop
wLine := field1 || field2 || field3;
dbms_lob.writeappend(l_clob, length(wLine), wLine);
--Inside for download, if you reach 10 records, download and continue.
if wCont = 10 then
dbms_lob.createtemporary(lob_loc => l_blob, cache => true, dur => dbms_lob.call);
sys.htp.init;
sys.owa_util.mime_header( 'text/xml', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
sys.htp.p('Content-Disposition: attachment; filename="' || SUBSTR(wFile || '.XML', INSTR(wFile|| '.XML', '/') + 1) || '');
sys.htp.p('Cache-Control: max-age=3600');
sys.owa_util.http_header_close;
l_dest_offset := 1;
l_src_offset := 1;
l_lang_context := dbms_lob.default_lang_ctx;
dbms_lob.converttoblob (l_blob, l_clob, DBMS_LOB.LOBMAXSIZE, l_dest_offset, l_src_offset, DBMS_LOB.DEFAULT_CSID, l_lang_context, v_warning);
sys.wpg_docload.download_file(l_blob);
DBMS_LOB.FREETEMPORARY (l_blob);
end if;
end loop;
--At the end it downloads the rest that is left.
dbms_lob.createtemporary(lob_loc => l_blob, cache => true, dur => dbms_lob.call);
sys.htp.init;
sys.owa_util.mime_header( 'text/xml', FALSE,'UTF-8' );
sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( l_clob ));
sys.htp.p('Content-Disposition: attachment; filename="' || SUBSTR(wFile || '.XML', INSTR(wFile|| '.XML', '/') + 1) || '');
sys.htp.p('Cache-Control: max-age=3600');
sys.owa_util.http_header_close;
l_dest_offset := 1;
l_src_offset := 1;
l_lang_context := dbms_lob.default_lang_ctx;
dbms_lob.converttoblob (l_blob, l_clob, DBMS_LOB.LOBMAXSIZE, l_dest_offset, l_src_offset, DBMS_LOB.DEFAULT_CSID, l_lang_context, v_warning);
sys.wpg_docload.download_file(l_blob);
DBMS_LOB.FREETEMPORARY (l_blob);
apex_application.stop_apex_engine;
end loop;
It's just downloading the last part.
pl/sql version.: 14
DB version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Thank you all.