Re: commit inside a cursor oper

sahmad_at_mfa.com (Saad Ahmad) wrote:

>What kind of problems can arise if a code
>segment is doing a commit inside a cursor op.
>ie.
>
>OPEN CURSOR c1;
>
>for(;;)
>{
> FETCH c1;
>
> some update;
> commit;
>
>}
>
>CLOSE c1;
>
>I know this should not be done; but what problems
>can arise in such a case? eg. locks lost etc.
>
The main problem I've experienced with this construct occurs when the update is against the same table(s) referenced in the cursor. The problem isn't really the commit itself, it's the read consistency model applied to open cursors. The commit does not appear to release rollback segments associated with the cursor and (given enough volume) the dynamic rebuilding of modified blocks (for read consistency) will cause Oracle to choke.

Ken friday

-- 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Q: How many Zen masters does it take to screw in a light bulb?
 
A: None.
   The Universe spines the bulb, and
   the Zen master stays out of the way.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~