ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一个有趣的错误ORA-1002

一个有趣的错误ORA-1002

原创 Linux操作系统 作者:yangtingkun 时间:2008-03-10 23:58:16 0 删除 编辑

今天同事发现了一个错误,错误号为ORA-1002

 

 

为了更好展示这个问题,构造了下面的代码了重现问题:

SQL> CREATE TABLE T (ID NUMBER);

表已创建。

SQL> CREATE TABLE T1 (ID NUMBER);

表已创建。

SQL> INSERT INTO T VALUES (1);

已创建 1 行。

SQL> INSERT INTO T1 SELECT ROWNUM FROM TAB WHERE ROWNUM < 4;

已创建3行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T;

        ID
----------
         1

SQL> SELECT * FROM T1;

        ID
----------
         1
         2
         3

下面只需要执行下面的PL/SQL语句,就可以重现问题:

SQL> BEGIN
  2   UPDATE T SET ID = ID;
  3   FOR I IN (SELECT ID FROM T1) LOOP
  4    IF I.ID = 2 THEN
  5     ROLLBACK;
  6    END IF;
  7   END LOOP;
  8  END;
  9  /
BEGIN
*
1 行出现错误:
ORA-01002:
读取违反顺序

ORA-06512:
line 3

看来是由于ROLLBACK语句影响了FOR循环中CURSOR的状态。如果去掉ROLLBACK语句或者去掉FOR语句前面的UPDATE语句,都是不会报错的。

SQL> BEGIN
  2   FOR I IN (SELECT ID FROM T1) LOOP
  3    IF I.ID = 2 THEN
  4     ROLLBACK;
  5    END IF;
  6   END LOOP;
  7  END;
  8  /

PL/SQL 过程已成功完成。

SQL> BEGIN
  2   UPDATE T SET ID = ID;
  3   FOR I IN (SELECT ID FROM T1) LOOP
  4    IF I.ID = 2 THEN
  5     NULL;
  6    END IF;
  7   END LOOP;
  8  END;
  9  /

PL/SQL 过程已成功完成。

SQL> ROLLBACK;

回退已完成。

如果在UPDATE语句后面添加COMMIT,也是不会报错的:

SQL> BEGIN
  2   UPDATE T SET ID = ID;
  3   COMMIT;
  4   FOR I IN (SELECT ID FROM T1) LOOP
  5    IF I.ID = 2 THEN
  6     ROLLBACK;
  7    END IF;
  8   END LOOP;
  9  END;
 10  /

PL/SQL 过程已成功完成。

基本上可以确认问题是由于ROLLBACK需要回滚CURSOR之前的DML,导致Oracle改变了CURSOR本身的状态。

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 -
Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

这个问题发生在920410gFOR循环解决了这个问题:

SQL> CONN YANGTK/YANGTK@YTK102
已连接。
SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE TABLE T (ID NUMBER);

表已创建。

SQL> CREATE TABLE T1 (ID NUMBER);

表已创建。

SQL> INSERT INTO T VALUES (1);

已创建 1 行。

SQL> INSERT INTO T1 SELECT ROWNUM FROM TAB WHERE ROWNUM < 4;

已创建3行。

SQL> COMMIT;

提交完成。

SQL> SELECT * FROM T;

        ID
----------
         1

SQL> SELECT * FROM T1;

        ID
----------
         1
         2
         3

SQL> BEGIN
  2   UPDATE T SET ID = ID;
  3   FOR I IN (SELECT ID FROM T1) LOOP
  4    IF I.ID = 2 THEN
  5     ROLLBACK;
  6    END IF;
  7   END LOOP;
  8  END;
  9  /

PL/SQL 过程已成功完成。

不过,只需要将FOR循环游标改为用户声明并FETCH的游标,文件就会重新:

SQL> DECLARE
  2   CURSOR C_CURSOR IS SELECT ID FROM T1;
  3   I C_CURSOR%ROWTYPE;
  4  BEGIN
  5   UPDATE T SET ID = ID;
  6   OPEN C_CURSOR;
  7   FETCH C_CURSOR INTO I;
  8   LOOP
  9    EXIT WHEN C_CURSOR%NOTFOUND;
 10    IF I.ID = 2 THEN
 11     ROLLBACK;
 12    END IF;
 13    FETCH C_CURSOR INTO I;
 14   END LOOP;
 15   CLOSE C_CURSOR;
 16  END;
 17  /
DECLARE
*
1 行出现错误:
ORA-01002:
提取违反顺序

ORA-06512:
line 13

10G中虽然修正了这个bug,但是修改的并不彻底。在11g中,这个问题和10g中一样。在Metalink上也没有看到Oracle对这个bug有相关的描述。

这个bug也很容易避免,除了上面的几种写法外,推荐一种更合理的做法:

SQL> BEGIN
  2   UPDATE T SET ID = ID;
  3   FOR I IN (SELECT ID FROM T1) LOOP
  4    IF I.ID = 2 THEN
  5     RAISE_APPLICATION_ERROR(-20000, 'USER_ERR');
  6    END IF;
  7   END LOOP;
  8  EXCEPTION
  9   WHEN OTHERS THEN
 10    ROLLBACK;
 11  END;
 12  /

PL/SQL 过程已成功完成。

这才是一种合理的异常处理方法,而例子中采用的在循环中回滚的方式本身就是不推荐的。

 

 

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10366963