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

原创 2006年05月25日 20:31:00

官方解释:

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 状态)。

相关文章推荐

oracle ORA-01002: fetch out of sequence tips 违反提取顺序

The Oracle oerr utility  notes this on the ora-01002 error: ORA-01002: fetch out of sequence Cau...

异常fetch out of sequence的生成原因

主要是因为使用ExpressDSO.save()(泛指一些需要事务控制,即可提交和回滚支持的方法)时没有将其作为事务提交,在spring.xml里经过以下配置后就可以了          class=...

ORA-01002 fetch out of sequence

 ORA-01002        fetch out of sequence Cause        In a host language program, a FETCH call...

【问题记录】ORA-04031 – shared_pool out of memory

ERROR: ORA-04031: unable toallocate 28064 bytes of shared memory ("shared pool","unknownobject","sga...

ORACLE ORA-04030之 out of process memory when trying to allocate

近期巡检中,一oracle 11g rac节点出现ORACLE ORA-04030之 out of process memory when trying to allocate报错,查询ORACLE官...

ORA-27102:out of memory Linux-X86_64 Error: 28: No space left on device

startup Linux下oracle报以下错 ORA-27102:out of memory Linux-X86_64 Error: 28: No space left on device ...

关于oracle 使用大内存出现:ORA-27102: out of memory

现象: alter system set sga_max_size=12884901888 scope=spfile      ---(12G) 出现:  SQL >startup force ...
  • gyanp
  • gyanp
  • 2011年11月21日 16:58
  • 19873

ORA-27102: out of memory (调整SGA时)

--oracle user and login in database with as sysdba SQL> show parameter log_buff NAME                ...

While starting out of two DBs one DB getting error: ORA-01102: cannot mount database in EXCLUSIVE mo

While starting out of two DBs one DB getting error: ORA-01102: cannot mount database in EXCLUSIVE mo...
  • loryliu
  • loryliu
  • 2016年09月06日 16:50
  • 347

ORA-00314: log 20 of thread 1, expected sequence# 108055 doesn't match 108053

转载请注明出处:http://blog.csdn.net/xiaofan23z Ora-00314 Symptom:   *** 2012-03-30 16:55:32.921 ...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:主题:ORA-01002: fetch out of sequence问题
举报原因:
原因补充:

(最多只允许输入30个字)