Re: why 1002-fetch out of sequence?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 11 Feb 1999 20:22:32 GMT
Message-ID: <36c43b2b.15090278@192.86.155.100>

A copy of this was sent to "Andrew P.Sandimirov" <A.Sandimirov_at_vaz.ru> (if that email address didn't require changing) On Thu, 11 Feb 1999 09:53:17 +0400, you wrote:

>Hello All.
>Why in IF block commit make error:
>ORA-01002: fetch out of sequence
>

because you are commiting and then trying to fetch from a FOR UPDATE cursor. All locks are released on commit, a for update cursor has locks -- therefore it is closed.

it is bad practice to commit inside of a cursor for loop when you are modifying the table you are selecting from inside the loop in any case. You might think you need less rollback this way, however you will tend to get yourself into an ORA-1555 error very quickly (you see, you really DO need that rollback -- your query needs the rollback to get a consistent view of the underlying table).

In any event, if you want to try it (but it will be subject to ora-1555 very easily) you would want to:

  for x in ( select ns.*, rowid rid, rowcount rcnt from ns where ... )   loop

      update ns set prim = prim where rowid = x.rid;
      if mod(x.rcnt,100) = 0 
      then
           commit;
      end if;

  end loop;

but i wouldn't recommend it.             

>DECLARE
> err number;
> CURSOR NS1 IS SELECT PRIM FROM NS where ndet like '21%' FOR UPDATE
>OF PRIM;
> NS_RECORD NS1%ROWTYPE;
>BEGIN
>OPEN NS1;
>LOOP
>FETCH NS1 INTO NS_RECORD;
>EXIT WHEN NS1%NOTFOUND;
> update NS set PRIM = PRIM WHERE CURRENT OF NS1;
> IF MOD(ns1%rowcount,100) = 0 THEN
> COMMIT WORK; /* it is HERE*/
> END IF;
> END LOOP;
>CLOSE NS1;
>EXCEPTION
>WHEN OTHERS THEN
>ERR:=SQLCODE;
>INSERT INTO A(POLE) VALUES (ERR);
>END;
>/
>
 

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

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Feb 11 1999 - 14:22:32 CST