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

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 ;