Oracle ora-01002

ORA-01002:fetch超出序列
（fetch out of sequence）

原因（Cause）: 这个错误的意思是获取（fetch）游标，但游标无效。注意，PL/SQL隐含游标循环不能fetch，从而导致此错误。该错误可能包括下面原因：
（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)最后一行已经检索后，获取（fetch）游标。返回ORA-1403错误。
（Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned.）

2)如果游标已经被FOR UPDATE 字句打开，发出COMMIT以后fetch将会返回错误。
（If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error.）

3) 在SQL语句中重新绑定任何占位符，然后在重新执行前发出fetch语句。
（Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.）

操作（Action）:

1)在最后行已被检索后，不要发出fetch语句。没有更多的行需要fetch。
（Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch.）
2) 不要在fetch循环游标中发出COMMIT，它已经打开FOR UPDATE时。
（Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.）
3) 后重新绑定后重新执行语句，然后尝试再次fetch。
（Reexecute the statement after rebinding, then attempt to fetch again.）


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

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

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 过程已成功完成。

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





ORA-01002:

fetch out of sequence



Cause:

In a host language program, a FETCH call was issued out of sequence. A successful parse-and-execute call must be issued before a fetch. This can occur if an attempt was made to FETCH from an active set after all records have been fetched. This may be caused by fetching from a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop implicitly does fetches and may also cause this error.



Action:

Parse and execute a SQL statement before attempting to fetch the data.


实际应用及解决方法：
1、在你取完部分数据并执行的过程中，可能有commit或者rollback语句，导致在表t上加的lock被释放掉，再取数据的时候导致出错。

Fetching Across Commits
The FOR UPDATE clause acquires exclusive row locks. All rows are locked when you
open the cursor, and they are unlocked when you commit your transaction. So, you
cannot fetch from a FOR UPDATE cursor after a commit. If you do, PL/SQL raises an
exception. In the following example, the cursor FOR loop fails after the tenth insert:

DECLARE
CURSOR c1 IS SELECT ename FROM emp FOR UPDATE OF sal;
ctr NUMBER := 0;
BEGIN
FOR emp_rec IN c1 LOOP -- FETCHes implicitly
...
ctr := ctr + 1;
INSERT INTO temp VALUES (ctr, ’still going’);
IF ctr >= 10 THEN
COMMIT; -- releases locks
END IF;

END LOOP;
END;

If you want to fetch across commits, do not use the FOR UPDATE and CURRENT OF
clauses. Instead, use the ROWID pseudocolumn to mimic the CURRENT OF clause.
Simply select the rowid of each row into a UROWID variable. Then, use the rowid to
identify the current row during subsequent updates and deletes. An example
follows:
DECLARE
CURSOR c1 IS SELECT ename, job, rowid FROM emp;
my_ename emp.ename%TYPE;
my_job emp.job%TYPE;
my_rowid UROWID;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_job, my_rowid;
EXIT WHEN c1%NOTFOUND;
UPDATE emp SET sal = sal * 1.05 WHERE rowid = my_rowid;
-- this mimics WHERE CURRENT OF c1
COMMIT;
END LOOP;
CLOSE c1;
END;
2、禁用自动提交试试。如果你仍然有其他的行在查询的时候也禁用手工提交，当有ｆｏｒ　ｕｐｄａｔｅ游标仍然打开时执行的任何提交可能会造成这个错误。
setAutoCommit(false)

实际事例：

实际上，我们的一个客户正在使用Oracle 9.2作为后端。他想要从表中使用简单的SELECT (select * from state)语句从表中检索数据。他得到了一个错误信息：“ORA-01002: fetch out of sequence”。与此同时，我也在我的电脑上使用SQL*Plus，还有前端。我使用这两个都工作良好。只使用一个简单的select语句却得到了这种类型的错误信息，到底是什么原因呢？
你确定他没有使用select语句作为指针的部分吗？通常情况下，如果你在没有指定FOR UPDATE子句的时候，试图对那些SQL语句正在检索的数据行执行一个提交，就会出现这种错误，还有一种情况是，你在指针检索完最后一行之后再次执行这个语句，也会出现这个错误信息（在这种情况下，根据你的实际情况，指针被定义为select * from 状态）。