Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 'Fetch Out of Sequence'?
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;
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;
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
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 CST