Re: PL/SQL - Commits in a update cursor

A copy of this was sent to CJ <djjr_at_ix.netcom.com> (if that email address didn't require changing) On Wed, 28 Jul 1999 11:47:12 -0700, you wrote:

>Anyone have any sample code on how to commit every X,000 records in a
>update cursor?
>I get the following error : ORA-01002: fetch out of sequence
>Can it be done? My code looks something like this :
>
>For r_rec in rec_cursor loop
> /* Updates here */
>
> rec_cnt := rec_cnt + 1;
> if (rec_cnt mod 10000=0)
> commit;
>end if
>end loop;
>
>
>TIA
a commit always closes a FOR UPDATE cursor. No way around it.

committing inside a cursor for loop, if you are updating the table in the loop that you are querying, is a sure fire way to get a ORA-1555. Do you really need to commit inside that loop?

If you can safely commit in the loop, that tells me your process must be restartable (if the power goes out after you've committed some work but not finished yet, you must have some mechanism to pick up where you left off -- right?). If it is restartable, you might consider doing some work, committing, and 'restarting' the loop again. That way you can commit in the loop and you will not hit the ORA-1555 error.

-- 
See http://govt.us.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