Re: ORA01002 fetch out of sequence

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 27 May 1998 21:11:29 GMT
Message-ID: <356d8149.5129305@192.86.155.100>

A copy of this was sent to john0725_at_aol.com (John0725) (if that email address didn't require changing) On 27 May 1998 14:30:15 GMT, you wrote:

>Help!
>
>Does anybody know why I get this?
>
>I am opening a simple, unparamaterized cursor of a small (8 fields, maybe 20
>records) unindexed table inside a stored procedure.
>
>I have declared the cursor before the BEGIN line,
>
>then I say
>
>OPEN cursor_name;
>
>if cursor_name%isOpen then
> Loop
> Fetch cursor_name into list_of_variables;
> if cursor_name%NotFound then
> exit;
> else
> other statements....
>
>

Are you using a select for update and committing somewhere? For example:

  1      DECLARE
  2         EMP_NAME EMP.ENAME%TYPE;
  3         CURSOR C1 IS SELECT ENAME FROM EMP for update;
  4      BEGIN
  5         OPEN C1;
  6         LOOP
  7             if ( c1%isopen ) then
  8               FETCH C1 INTO EMP_NAME;
  9               exit when c1%notfound;
 10               commit;
 11             end if;
 12         END LOOP;
 13         CLOSE C1;
 14*     END;

SQL> /
    DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 8

this always fails on the 2'cnd fetch...

>
>but when it gets to the fetch line I get the error...
>
>
>any ideas? I have done this sort of thing exactly the same way on other
>tables before and it worked fine until now.
>
>
>thanks,
>
>John
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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 Wed May 27 1998 - 16:11:29 CDT