主題:ORA-01002: fetch out of sequence問題

官方解釋：

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 狀態）。