关闭

主题:ORA-01002: fetch out of sequence问题

标签: insertsqlactionoracleceach
13219人阅读 评论(0) 收藏 举报
分类:

官方解释:

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、禁用自动提交试试。如果你仍然有其他的行在查询的时候也禁用手工提交,当有for update游标仍然打开时执行的任何提交可能会造成这个错误。
setAutoCommit(false)

实际事例:

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

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:796850次
    • 积分:10413
    • 等级:
    • 排名:第1646名
    • 原创:237篇
    • 转载:184篇
    • 译文:0篇
    • 评论:175条
    最新评论