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 Date:      09-Apr-1999
rem Author:    Frank Naude (frank@onwe.co.za)
rem -----------------------------------------------------------------------

declare
  i number := 0;
  cursor s1 is SELECT * FROM tab1 WHERE col1 = 'value1'
               FOR UPDATE;
begin
  for c1 in s1 loop
      update tab1 set col1 = 'value2'
             where current of s1;

      i := 1 + 1;              -- Commit after every X records
      if i > 1000 then
         commit;
         i := 0;
      end if;

  end loop;
  commit;
end;
/