HELP: Commits Inside Cursor Loops

Hello All,

Can someone explain to me the effect of having commits inside cursor loops? How does that affect resources (memory), locks, ..., etc? Also, what are the differences between the two different approaches below when coding cursor loops for updates? We are seeing weird behaviors when using approach #2 (such as same record being processed multiple times, and out of memory errors).

T.I.A.

Francis

Approach #1

cursor l_cursor is select ... from tbl where .... for update of col nowait;

for l_rec in l_cursor loop
...

   update tbl set ... where current of l_cursor;    ...
   commit;
end loop;

Approach #2

cursor l_cursor is select rowid row_id, ... from tbl where ....;

for l_rec in l_cursor loop
...

   update tbl set ... where rowid = l_rec.row_id;    ...
   commit;
end loop; Received on Tue Dec 07 1999 - 01:44:46 CST