Re: HELP: Commits Inside Cursor Loops

I think a commit inside the cursor loop closes the cursor. If your doing a lot of updates you'll want to reduce the number of commits, while also
ensuring that you don't have too many uncommitted updates to ensure you don't run
out of rollback segments.
Our practice was to build an outer control loop using a counter or a data condition from the table
and moving the commit outside the cursor loop. That way you can tune the routine for optimum updates
and minimum commits. It generally avoids the "Snapshot too old error' too.

Hope this helps

Regards

Mark Brayshaw
Consultant,
Interim Technology
Level 4, 11 Harvest Terrace, West Perth WA 6005, AUSTRALIA Perth: Ph: +61 8 9481 0488 Fax: +61 8 9481 6576 Mobile: 0409 405 411
mailto:markbrayshaw_at_interim.com
http://www.interimtechnology.com.au

Transforming the way people work with technology with PERFORMANCE - TEAMWORK - LEARNING - INNOVATION - INTEGRITY

Francis Chang <tassale_at_best.com> wrote in message news:384CBAEE.D81CA864_at_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 - 22:36:55 CST