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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1828362/,如需转载,请注明出处,否则将追究法律责任。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 猿与汪的秘密 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值