Hi,
I have a process that generates two million records into a temporary table (forecasted to increase to 9 million).The data from the temporary table should then be copied into a CLOB and stored back into a database column.
This report will then be downloaded from the Apex page.
At present I am facing performance issues with copying the data from the table into a CLOB column which takes 12mins to complete the process. Code snippet is given below.
Is there any better way of doing this ?
Code Snippet
DECLARE
lv_clob_temp CLOB;
lv_content_file REPORT_DETAILS.content_file%type;
lv_rev_header_file REPORT_DETAILS.rev_header_file%type;
lv_rev_content_file REPORT_DETAILS.rev_content_file%type;
type rec_report_temp is table of REPORT_TEMP%ROWTYPE;
tbl_report_temp rec_report_temp;
lv_bulk_limit pls_integer := 100000;
BEGIN
-- fetch master data for the report
SELECT
content_file
, rev_content_file
INTO
lv_content_file
, lv_rev_content_file
FROM REPORT_DETAILS
WHERE rep_id = 1 for update;
dbms_lob.createtemporary(lv_clob_temp, TRUE, DBMS_LOB.SESSION );
--Data for the content extract
open cur_rec for 'SELECT COL1, COL2, COL3, COL4, COL5, COL6, COL7 from REPORT_TEMP';
loop
FETCH cur_rec BULK COLLECT INTO tbl_report_temp LIMIT lv_bulk_limit;
EXIT WHEN tbl_report_temp.count = 0;
for i in 1..tbl_report_temp.count
loop
lv_clob_temp := tbl_report_temp(i).COL1
||','||tbl_report_temp(i).COL2
||','||to_char(tbl_report_temp(i).COL3,'DD/MM/YYYY')
||','||to_char(tbl_report_temp(i).SPAD,'DD/MM/YYYY')
||','||to_char(tbl_report_temp(i).COL5,'DD/MM/YYYY')
||','||tbl_report_temp(i).COL7
||','||to_char(tbl_report_temp(i).COL6,'DD/MM/YYYY')
|| lv_crlf;
DBMS_LOB.APPEND(lv_content_file, lv_clob_temp);
lv_clob_temp := tbl_report_temp(i).COL1
||','||tbl_report_temp(i).COL2
||','||to_char(tbl_report_temp(i).COL3,'DD/MM/YYYY')
||','||to_char(tbl_report_temp(i).SPAD,'DD/MM/YYYY')
||','||to_char(tbl_report_temp(i).COL5,'DD/MM/YYYY')
|| lv_crlf;
DBMS_LOB.APPEND(lv_rev_content_file, lv_clob_temp);
end loop;
end loop;
UPDATE REPORT_DETAILS
set content_file = lv_content_file
, rev_content_file = lv_rev_content_file
WHERE rep_id = 1;
COMMIt;
END;