Re: HELP: Commits Inside Cursor Loops

Definately should not go with no. one. A for update cursor loop is invalidated when a commit occurs. Why? A for update locks the rows selected in the cursor, as they are intended to be updated. When a commit occurs the locks are then released, the cursor then becomes invalid as it has lost it's place - it now no longer knows which rows to get next without those locks.

Option two is better, even more so with a control on when the commit occurs.

Regards

Haroon Khalique

Kai Poitschke wrote:
>
> You should definitly go with #1. This is what oracle recons for this type
> of processing.
>
> Kai
> --
> $f
>
> Unix, WinNT and MS-DOS. The Good, The Bad and The Ugly.
> Kai Poitschke MailTo:kai.poitschke[at]computer.org
> Date/Time: $d/$t

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 Wed Dec 08 1999 - 02:02:13 CST