rem ----------------------------------------------------------------------- rem Filename: plsloop.sql rem Purpose: Example: UPDATE/DELETE in a loop and commit very X records rem Handy for huge tables that cause rollback segment problems rem DON'T ISSUE COMMIT TOO FREQUENTLY! rem Date: 09-Apr-1999; Updated: 25-Nov-2004 rem Author: Frank Naude, Oracle FAQ rem ----------------------------------------------------------------------- declare i number := 0; cursor s1 is SELECT rowid, t.* FROM tab1 t WHERE col1 = 'value1'; begin for c1 in s1 loop update tab1 set col1 = 'value2' where rowid = c1.rowid; i := i + 1; -- Commit after every X records if i > 10000 then commit; i := 0; end if; end loop; commit; end; / -- Note: More advanced users can use the mod() function to commit every N rows. -- No counter variable required: -- -- if mod(i, 10000) -- commit; -- dbms_output.put_line('Commit issued for rows up to: '||c1%rowcount); -- end if; --