You Asked
Hi Tom , I am bit new to the PLSQL world and trying to write a block that will read clob data and update(only first 4000 byte as we are still using 11g) to a varchar column . The block is ready but when I execute with some good number(close to 70 K) of rows it does not seem to be coming out of the loop also the performance is very bad . Is there a suggestion you can make ? Here is the code :
Set serveroutput on;
declare
TYPE CMPLN_temp_clob_aat IS TABLE OF CMPLN_temp_clob%ROWTYPE;
l_CMPLN_temp_clob CMPLN_temp_clob_aat;
CURSOR CMPLN_temp_clob_cur IS SELECT * FROM QAD_CMS.CMPLN_temp_clob;
l_data_1 VARCHAR2(32767);
l_data_2 VARCHAR2(32767);
l_data_3 VARCHAR2(32767);
l_data_4 VARCHAR2(32767);
l_data_5 VARCHAR2(32767);
l_data_6 VARCHAR2(32767);
l_data_7 VARCHAR2(32767);
l_data_8 VARCHAR2(32767);
l_data_9 VARCHAR2(32767);
begin
OPEN CMPLN_temp_clob_cur;
LOOP
Fetch CMPLN_temp_clob_cur BULK COLLECT into l_CMPLN_temp_clob LIMIT 5000;
FOR indx IN 1 .. l_CMPLN_temp_clob.COUNT
Loop
l_data_1 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).ASSESS_QSNR, 4000, 1 ),1,4000);
l_data_2 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).CLSR_SUMRY, 4000, 1 ),1,4000);
l_data_3 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).EVAL_RTNL, 4000, 1 ),1,4000);
l_data_4 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).FLLW_UP_INFO, 4000, 1 ),1,4000);
l_data_5 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).SMPL_CMNT, 4000, 1 ),1,4000);
l_data_6 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).SRC_SYS_DESCR, 4000, 1 ),1,4000);
l_data_7 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).MED_SFTY_RVW, 4000, 1 ),1,4000);
l_data_8 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).ADDL_MED_SFTY_RVW, 4000, 1 ),1,4000);
l_data_9 := substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).EVNT_DESCR, 4000, 1 ),1,4000);
update
QAD_CMS.CMPLN_temp_clob set
ASSESS_QSNR_SHRT=l_data_1,
CLSR_SUMRY_SHRT=l_data_2 ,
EVAL_RTNL_SHRT=l_data_3,
FLLW_UP_INFO_SHRT=l_data_4,
SMPL_CMNT_SHRT=l_data_5,
SRC_SYS_DESCR_SHRT=l_data_6,
MED_SFTY_RVW_SHRT=l_data_7,
ADDL_MED_SFTY_RVW_SHRT=l_data_8,
EVNT_DESCR_SHRT=l_data_9
where CMPLN_pr_id=l_CMPLN_temp_clob(indx).CMPLN_pr_id;
commit;
END LOOP;
commit;
exit when CMPLN_temp_clob_cur%notfound;
END LOOP;
CLOSE CMPLN_temp_clob_cur;
commit;
end ;
Also the reason I using a PLSQL block is because we have multi byte characters in the source so the substr(clob,1,4000) not always gives 4000 bytes of data
and Connor said...
Try something like this (obviously I cant compile this on my db, but key points are)
- use the cursor to pick up the data rather than doing all those assigments
- bulk bind update
- no unnecessary commits
Set serveroutput on;
declare
cursor c is
SELECT CMPLN_pr_id,
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).ASSESS_QSNR, 4000, 1 ),1,4000) c1
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).CLSR_SUMRY, 4000, 1 ),1,4000) c2
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).EVAL_RTNL, 4000, 1 ),1,4000) c3
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).FLLW_UP_INFO, 4000, 1 ),1,4000) c4
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).SMPL_CMNT, 4000, 1 ),1,4000) c5
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).SRC_SYS_DESCR, 4000, 1 ),1,4000) c6
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).MED_SFTY_RVW, 4000, 1 ),1,4000) c7
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).ADDL_MED_SFTY_RVW, 4000, 1 ),1,4000) c8
substrb(dbms_lob.substr(l_CMPLN_temp_clob(indx).EVNT_DESCR, 4000, 1 ),1,4000) c9
FROM QAD_CMS.CMPLN_temp_clob;
TYPE CMPLN_temp_clob_aat IS TABLE OF c%ROWTYPE index by pls_integer;
l_CMPLN_temp_clob CMPLN_temp_clob_aat;
open c;
loop
fetch c BULK COLLECT into l_CMPLN_temp_clob LIMIT 5000;
exit when l_CMPLN_temp_clob.count = 0;
forall indx in 1 .. l_CMPLN_temp_clob.count
update QAD_CMS.CMPLN_temp_clob
set
ASSESS_QSNR_SHRT=l_CMPLN_temp_clob(indx).c1,
CLSR_SUMRY_SHRT=l_CMPLN_temp_clob(indx).c2,
EVAL_RTNL_SHRT=l_CMPLN_temp_clob(indx).c3,
FLLW_UP_INFO_SHRT=l_CMPLN_temp_clob(indx).c4,
SMPL_CMNT_SHRT=l_CMPLN_temp_clob(indx).c5,
SRC_SYS_DESCR_SHRT=l_CMPLN_temp_clob(indx).c6,
MED_SFTY_RVW_SHRT=l_CMPLN_temp_clob(indx).c7,
ADDL_MED_SFTY_RVW_SHRT=l_CMPLN_temp_clob(indx).c8,
EVNT_DESCR_SHRT=l_CMPLN_temp_clob(indx).c9
where CMPLN_pr_id=l_CMPLN_temp_clob(indx).CMPLN_pr_id;
END LOOP;
commit;
CLOSE CMPLN_temp_clob_cur;
end ;
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment