Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: HELP: Commits Inside Cursor Loops

Re: HELP: Commits Inside Cursor Loops

From: Kenneth C Stahl <BlueSax_at_Unforgettable.com>
Date: Wed, 08 Dec 1999 09:25:55 -0500
Message-ID: <384E6A73.B8955C51@Unforgettable.com>


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 Wed Dec 08 1999 - 08:25:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US