Re: HELP: Commits Inside Cursor Loops

From: Francis Chang <tassale_at_best.com>
Date: Wed, 08 Dec 1999 23:21:22 -0800
Message-ID: <384F5872.A9A1F7CA@best.com>

Thanks to everyone's input.

What we'll do is partition the data to be processed and use a counter loop outside of the cursor loop. We can then issue commits outside the cursor loop (inside the counter) to manage the rollback segment.

Francis

Kenneth C Stahl wrote:

> Thomas Kyte wrote:
> >
> > 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

> I didn't read close enough to notice the "for update of/where current of".
> Personally I never use those because there are to many "gotchas" and I
> prefer to micro-manage locks myself if they are needed.

>


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................


> ...................................................................

> v
Received on Thu Dec 09 1999 - 01:21:22 CST