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 -> Re: HELP: Commits Inside Cursor Loops

Re: HELP: Commits Inside Cursor Loops

From: Ivan <ivan_at_protek.ru>
Date: Tue, 07 Dec 1999 19:40:21 +0300
Message-ID: <384D3875.872BC82A@protek.ru>


Hi, All!

I use Approach #2, but I do commit on each 100th step

i number(8);

i := i+1;

if mod(i,100)=0 .....

Best regards,
Ivan


Francis Chang wrote:

> 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 - 10:40:21 CST

Original text of this message

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