一个有趣的错误ORA-1002

今天同事发现了一个错误，错误号为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;

        IDITPUB个人空间ZGy7\[.L
y*?R
----------ITPUB个人空间KEI]xiohD
         1

SQL> SELECT * FROM T1;

        IDITPUB个人空间a e4yC&fa
----------
"T"z(w
tJ)|0         1ITPUB个人空间W/r/D
@H
         2ITPUB个人空间5lg.~v"T/n%s
         3

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

SQL> BEGIN
,B3hfK(R0  2   UPDATE T SET ID = ID;
w"e[4ld/mjJ0  3   FOR I IN (SELECT ID FROM T1) LOOPITPUB个人空间5V Sg7]-JF
  4    IF I.ID = 2 THEN
Q)Y#f6SM(Quz/Q0  5     ROLLBACK;ITPUB个人空间$^l!G1Z_2tS6h
  6    END IF;
)GAPf \ @v2Oc0  7   END LOOP;
3w%j.A:x6U6H0  8  END;ITPUB个人空间.@p$v.Aq9@X
  9  /ITPUB个人空间4R
gZt!z \E5gl
BEGIN
I~u1DEy7XEk0*ITPUB个人空间 bjY:yD F]
@
第1行出现错误:
9Y;X*f vf0ORA-01002:读取违反顺序ITPUB个人空间"pKz(^1Cw+L
ORA-06512:在line 3

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

SQL> BEGINITPUB个人空间b6q;g@7U8S+D^
  2   FOR I IN (SELECT ID FROM T1) LOOP
Aj6~;o.}cRz0  3    IF I.ID = 2 THEN
p8n;oDJLD7c+G*{0  4     ROLLBACK;ITPUB个人空间4X+^*lP7ova
  5    END IF;
*E
K6CU)e|{^:z0  6   ENDLOOP;
JJT2y*gS cU P0  7  END;
@7m|)hM&ii4{
T q0  8  /

PL/SQL过程已成功完成。

SQL> BEGINITPUB个人空间5c#Q;{([4A#i%@
  2   UPDATE T SET ID = ID;ITPUB个人空间"O"Ao/~;Jm
  3   FOR I IN (SELECT ID FROM T1) LOOP
]U B b5Ol b0  4    IF I.ID = 2 THENITPUB个人空间y|,p j/`
  5     NULL;
5S
b9] E
CDC0  6    END IF;ITPUB个人空间*i1Tw0g ND*VO
  7   END LOOP;ITPUB个人空间sCfHCli&G4v
M
  8  END;
^Ve@&Q!l {0  9  /

PL/SQL过程已成功完成。

SQL> ROLLBACK;

回退已完成。

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

SQL> BEGINITPUB个人空间;m8sQwtW
  2   UPDATE T SET ID = ID;
pwDjr&j7[0  3   COMMIT;ITPUB个人空间 DD0muG'@%G0h
  4   FOR I IN (SELECT ID FROM T1) LOOP

F&vUt9R0  5    IF I.ID = 2 THEN
-IcK.|"{
~!~i0  6     ROLLBACK;ITPUB个人空间&tS8C*nm? ON,F*tZ
  7    END IF;
ZoMZ9o ?f6V0r*P0  8   END LOOP;
@Ed&[4cX6B^R:A0  9  END;ITPUB个人空间?m h8["B p3h\
 10  /

PL/SQL过程已成功完成。

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

SQL> SELECT * FROM V$VERSION;

BANNER
.L~:bG!d0@tz{0----------------------------------------------------------------ITPUB个人空间0@1Bx:w(U:H
Oracle9i Enterprise Edition Release9.2.0.4.0 -Production
*jb E:?:r!m.W0PL/SQL Release 9.2.0.4.0 - ProductionITPUB个人空间&Y1d/VId9D
CORE    9.2.0.3.0       ProductionITPUB个人空间 I_8V_mg
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
(Q\.i-` Z}pze O0NLSRTL Version 9.2.0.4.0 - Production

这个问题发生在9204，10g的FOR循环解决了这个问题：

SQL> CONN YANGTK/YANGTK@YTK102ITPUB个人空间u r)B8}*YY)m-?O
已连接。
7PF l\!F h0SQL> SELECT * FROM V$VERSION;

BANNERITPUB个人空间H3d1Y!F-W N i
----------------------------------------------------------------
6Z2mAm9w4iZ$N0Oracle Database10gEnterprise Edition Release10.2.0.1.0 - Prod
x+e+FX;oU
Y
h0PL/SQL Release 10.2.0.1.0 - Production
1x,f.f/[~/g"h Z0CORE    10.2.0.1.0      ProductionITPUB个人空间H-gu?Ax)L
TNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionITPUB个人空间-A"u-L/L Ws~)_
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;

        IDITPUB个人空间8k]
WY FV
----------
RfL CQ(W${0         1

SQL> SELECT * FROM T1;

        ID
'F/Qm'@yqE;tj0----------
9m(s4L].? u0         1ITPUB个人空间tFbN l`9Q&kJf
         2ITPUB个人空间OyI8_J.h @$d4\!p
         3

SQL> BEGIN
1| Ge[b9g"eU3o0  2   UPDATE T SET ID = ID;ITPUB个人空间9wX` wI7liH(n(C0V
  3   FOR I IN (SELECT ID FROM T1) LOOPITPUB个人空间1x"I1S U"M"Rz#xb)w
  4    IF I.ID = 2 THENITPUB个人空间?9[9P9G IJQh6G
  5     ROLLBACK;ITPUB个人空间Iqp1O?C
  6    END IF;
:i&|b2R6q\v(Y0  7   END LOOP;
,Ld!] ~3eN0  8  END;
&B~TMU5K0  9  /

PL/SQL过程已成功完成。

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

SQL> DECLARE
.Vd&}"w*j0  2   CURSOR C_CURSOR IS SELECT ID FROM T1;
_T-ea
~3b0  3   I C_CURSOR%ROWTYPE;ITPUB个人空间@L6U+w8T4a x5k V |
  4  BEGINITPUB个人空间XyjK/Tb F$a
  5   UPDATE T SET ID = ID;ITPUB个人空间 hr8}|7@"`
Eo&Ua
  6   OPEN C_CURSOR;
H,j-B4TOe7g"AQ(?&?0  7   FETCH C_CURSOR INTO I;ITPUB个人空间vTsP5@r#ht2H%Y
  8   LOOP
Y#B!`XT7p0  9    EXIT WHEN C_CURSOR%NOTFOUND;
T~(x7n:OS+~0 10    IF I.ID = 2 THEN
*T'r3I5Rw'@0 11     ROLLBACK;ITPUB个人空间K;a+F dw
 12    END IF;
&@0d d
@,_"Q;A0 13    FETCH C_CURSOR INTO I;
B+R%O lt&X-{0 14   END LOOP;
k|m$QISbk0 15   CLOSE C_CURSOR;ITPUB个人空间r#k#[1X~ W2J
 16  END;
]-v1A8|s"K0 17  /
P-zdL,\SG0DECLAREITPUB个人空间4Xn3[z` Y4_
*
$x}9h;DB/] p0第1行出现错误:ITPUB个人空间YpBIJ#q.xy/\#l^
ORA-01002:提取违反顺序
%o sG:_O/ba6R&z0ORA-06512:在line 13

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