Hi Team
I have cursor which takes table name and dynamically .Then another cursor should be opened for this table so these values are stored using bulk collect dyamically for different table with limit clause since each table has huge data. then inside this we need to use FOR All to update that table that dynamcially changes to update millions of records.Can you help me if below approach is correct and any other way to update millinos of records faster.currently it is taking 6 to hours to update.WHere condtion for update statement is not from table it is based on some value coming from input.i have not mentioned here.
Declare
cursor cgettabledetail is select table_name,column name ,column value from A
where owner='input onwer'
cur_able sys_refcursor;
--begin start here
--- FOR rettabledetails IN cgettabledetail LOOP
lsetolumnist1:= lsetolumnist1|| rettabledetails .column\_name|| '= ' || rettabledetails .columnvalue||',';
lsetolumnist2:=lsetolumnist2|| rettabledetails .columnname ||',';
tablename := rettabledetails .tablename ;
columnname := rettabledetails .columnname ;
sql:='select \* from '||**l\_tablename ;**
type dy_tab is table of l_tablename*rowtype;
tb dy_tab;
open cur_able for sql
loop
fetch cur_able bulk collect int tb limit 1000
exit when tb.Count=0
FORALL i in 1.. tb .count LOOP
EXECUTE IMMEDIATE
UPDATE ' || l_tablename ||
' SET ' || lsetolumnist1
|| 'cntl_process' || ' = ''' ||i_source||' Success.'||''','
|| 'cntl_user_id' || ' = ''' ||i_userid||''','
|| 'cntl_timestamp' || ' = ''' ||l_sysdate ||'''
WHERE ' || 'NVL(cntl_process,''A'') <> '''||i_source||'''
END ;
Commit;
Please help me here if above is correct and how to use dynamic table type .Let me me any option to update millions of records fast .it is taking so much time with direct update .