Re: 'Fetch Out of Sequence'?

I am not sure if you are still seeking an answer, amyway : ROLLBACK or COMMIT are transaction ending commands. Once a transaction ends, the assoicated cursors are closed. When you try to fetch from a closed cursor, you get the 'Fetch out of sequence'. If you have to perform a ROLLBACK, and still leave the cursor open, you can trick ORACLE into doing so. Create a procedure, which just has a ROLLBACK statement, and call this procedure where you are now issuing a ROLLBACK.
This will leave the cursor open and available for subsequent fetches, also your rollback would be performed. I have used a similar scenario for COMMITs, but I believe it should work for ROLLBACK also.

Hope this helps and please acknowledge with your results.

CM

In article <#efuP0lX9GA.352_at_nih2naaa.prod2.compuserve.com>,   Paul Swallow <113220.3573_at_CompuServe.COM> 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;
>
>
> --
> ------- -------- -------
> ------- -------- -------
> ------- -------- -------
> ------- -------- -------
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 10 1998 - 00:00:00 CDT