Re: 'Fetch Out of Sequence'?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/04/03
Message-ID: <3524f7d9.5799288_at_192.86.155.100>#1/1

A copy of this was sent to Paul Swallow <113220.3573_at_CompuServe.COM> (if that email address didn't require changing) On Thu, 02 Apr 1998 12:30:40 -0500, you wrote:

>Does anyone agree that the following nested loop should not
>produce a 'fetch-out-of-sequence' error when tables paul2 & paul3
>each have 2 rows 'Committed'? Unfortunately this error does
>occur!
>
 

>OPEN cur1;
>LOOP <<LOOP1>>
> FETCH cur1 INTO var1;
> EXIT WHEN cur1%NOTFOUND;
> OPEN cur2;
> LOOP <<LOOP2>>
> FETCH cur2 INTO var2;
> EXIT WHEN cur2%NOTFOUND;
> INSERT INTO PAUL VALUES('X');
> ROLLBACK;
> END LOOP LOOP2;
> CLOSE cur2;
>END LOOP LOOP1;
> CLOSE cur1;
>

It depends on how you declared the cursor. If it was FOR UPDATE, the rollback will un-open the cursor. If it was not FOR UPDATE it should work. The ROLLBACK/COMMIT releases all locks which a FOR UPDATE takes.

Here is an example where you cursor loops work across the rollback and don't work across the rollback depending on how the cursors where defined.

SQL> create table t1 ( x int );

Table created.

SQL> insert into t1 values ( 1 );

1 row created.

SQL> insert into t1 values ( 2 );

1 row created.

SQL>
SQL> create table t2 ( x int );

Table created.

SQL> insert into t2 values ( 1 );

1 row created.

SQL> insert into t2 values ( 2 );

1 row created.

SQL>
SQL> create table paul ( x varchar2(200) );

Table created.

SQL>
SQL> declare

  2          cursor cur1 is select x from t1;
  3          cursor cur2 is select x from t2;
  4          var1    number;
  5          var2    number;
  6  begin
  7          open cur1;
  8          loop <<loop1>>
  9              fetch cur1 into var1;
 10              exit when cur1%notfound;
 11              open cur2;
 12              loop <<loop2>>
 13                  fetch cur2 into var2;
 14                  exit when cur2%notfound;
 15                  insert into paul values ( 'x' );
 16                  rollback;
 17              end loop loop2;
 18              close cur2;
 19          end loop loop1;
 20          close cur1;

 21 end;
 22 /

PL/SQL procedure successfully completed.

SQL>
SQL> declare

  2          cursor cur1 is select x from t1 for update;
  3          cursor cur2 is select x from t2 for update;
  4          var1    number;
  5          var2    number;
  6  begin
  7          open cur1;
  8          loop <<loop1>>
  9              fetch cur1 into var1;
 10              exit when cur1%notfound;
 11              open cur2;
 12              loop <<loop2>>
 13                  fetch cur2 into var2;
 14                  exit when cur2%notfound;
 15                  insert into paul values ( 'x' );
 16                  rollback;
 17              end loop loop2;
 18              close cur2;
 19          end loop loop1;
 20          close cur1;

 21 end;
 22 /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 13  

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 Fri Apr 03 1998 - 00:00:00 CEST