Re: HELP: Commits Inside Cursor Loops

A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com> (if that email address didn't require changing) On Wed, 08 Dec 1999 08:13:24 -0500, you wrote:

>Mark Brayshaw wrote:
>>
>> 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.
>>
>
>That is incorrect. It does not close the cursor. Simple experiments would
>show that.

Simple experiments would show that he was half right. the original post has

> 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;
>

commiting a for update cursor will shut it down.

His advice is pretty much on -- committing in a cursor for loop that is updating the table the cursor is reading is ****bad**** and a sure way to get ORA-1555's

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 08 1999 - 07:21:30 CST