Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> HELP: Commits Inside Cursor Loops

HELP: Commits Inside Cursor Loops

From: Francis Chang <tassale_at_best.com>
Date: Mon, 06 Dec 1999 23:44:46 -0800
Message-ID: <384CBAEE.D81CA864@best.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US