ORA-01002 “fetch out of sequence”关于cursor的一个bug

    今天发现一个PLSQL脚本报ORA-01002和ORA-06512：

ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 8

    原来，脚本里包含两个表的两个cursor，然后分别对每个表打开cursor然后对此表做一些dml，每隔若干行rollback（这是因为此PLSQL脚本还在测试阶段，所以需要rollback）。

    然而，问题就出在，对表A作为dml后，在循环完成的最后没有写“rollback”，这就导致了对表A的一些dml在脚本打开表B的cursor前，并没有被rollback，于是报此错。

    举个简单的例子，例如cursor取出表A的1004行，然后每隔100行rollback一次，那么最后还有对最后4行的dml没有被rollback，接着打开另一个cursor时报错。

这个错，跟Bug 3039457: PL/SQL: FETCH FAILS WITH ORA-1002 AFTER ROLLBACK有一定联系。

    之所以，我提出这个错误号01002，是因为OERR ORA 1002的三个解释中都不包含这个bug。

 oerr ora 1002
01002, 00000, "fetch out of sequence"
// *Cause: This error means that a fetch has been attempted from a cursor
//         which is no longer valid.  Note that a PL/SQL cursor loop
//         implicitly does fetches, and thus may also cause this error.
//         There are a number of possible causes for this error, including:
//         1) Fetching from a cursor after the last row has been retrieved
//            and the ORA-1403 error returned.
//         2) If the cursor has been opened with the FOR UPDATE clause,
//            fetching after a COMMIT has been issued will return the error.
//         3) Rebinding any placeholders in the SQL statement, then issuing
//            a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
//             retrieved - there are no more rows to fetch.
//          2) Do not issue a COMMIT inside a fetch loop for a cursor
//             that has been opened FOR UPDATE.
//          3) Reexecute the statement after rebinding, then attempt to
//             fetch again.

    下面做一个很简单的实验解释这个bug。

    准备脚本test1.sql:

DECLARE
cursor c1 is select rowid
               from test1
              where ROWNUM <= 50000;
cnt number(38);
begin
   cnt := 0;
   for c1rec in c1 loop
      delete from test1
       where rowid = c1rec.rowid;

      cnt := cnt + 1;
      if mod(cnt, 77) = 0 then
        rollback;
      end if;
   end loop;
   rollback;
end;
/

    如上，我每隔77行rollback一次，对于一共50000行数据，肯定最后会剩下一些delete没有被rollback。如果我接着再run test1.sql，就会触发这个bug：

SQL> @test1

PL/SQL procedure successfully completed.

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

SQL> @test1

PL/SQL procedure successfully completed.

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

    解决方法很简单，就是在test1.sql的最后加上一句rollback就行了。

SQL> @test1

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> @test1

PL/SQL procedure successfully completed.