Hi All,
DB Version : 9.2.0.6.0
I have read much documentation on “commit inside loop” in this forum. But all of them related to Cursor for loops. I have slight different scenario here and, is this advisable to commit inside loop in below code. The table to be updated can contain total of 8000000 records. Each update query updates maximum of 6000 records in first run and lessens count of updates as it loop through it and eventually reaches to zero at which time it comes out of loop.
/*
This loop continue to run until it finds no records available to update by any of two queries written inside loop
Update of 1st query creates many records for 2nd query to update. Update of records by 2nd query creates many records for
1st query to update.
this loop continue to run until there are no more records to be updated by any of the queries
*/
LOOP
/* price_tbl is dynamic table created and managed during run time. Record insertion
to this table is done thru API before this loop*/
l_docid :=0;
l_id :=0;
/* this is first query */
L_QUERY := 'UPDATE ' ||price_tbl||' SET formed =''A'',flag = ''T''
where (id,type_1) in ( select id,type_1
from '||price_tbl||' where formed =''A''
intersect
SELECT ID,type_1 from ' ||price_tbl||' where formed =''P'') and formed =''P''';
EXECUTE IMMEDIATE l_query;
IF SQL%FOUND THEN
l_id :=1;
END IF;
COMMIT;
/* this is 2nd query */
l_query:= ' UPDATE ' ||price_tbl||' SET formed =''A'',flag = ''Y''
WHERE (docid,type_2) IN (
select docid,type_2 from ' ||price_tbl||' where formed =''A''
intersect
select docid,type_2 from ' ||price_tbl||' where formed =''P'') and formed =''P''';
EXECUTE IMMEDIATE l_query;
IF SQL%FOUND THEN
l_docid :=1;
END IF;
COMMIT;
/* Exit condition to come out of loop */
IF l_docid =0 AND l_id =0 THEN
EXIT;
END IF;
END LOOP;
Number of times that loop may execute varies. In worst case it loops 70-8- times.
Please advice is it wise to commit inside loop or should I commit after for loop ends.
Thanks in advance.
Regards,
Edited by: user07118719 on Sep 20, 2012 10:33 PM