Re: HELP: Commits Inside Cursor Loops

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