ORA-01002: fetch out of sequence问题

[rabbitbug]

如果我打开一个for update的cursor
而且打开的cursor中有好几条记录
不是唯一的
是不是这种情况不能用for update?

现在我运行这个存储过程时出错
ORA-01002: fetch out of sequence问题
在我把定义cursor中的for update去掉后就可以了

[CodeMagic]

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.

[rabbitbug]

对了，是这样的一个cursor
如下面
CURSOR myCursor IS
select c.*
from ( select *
from table t
order by t.sltDate ) c
where (rownum < 200)
for update;

[CodeMagic]

在你取完部分数据并执行的过程中，可能有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;

[rabbitbug]

谢谢 CodeMagic(ErrorDetector)
早上发现正是这个原因导致的错误
现在已经好了
不过现在还有一个问题是
我在cursor中用下面的select子句
还是出错
是不是由于select嵌套引起的？

CURSOR curStudSltCourse(years varchar2, termID varchar2) IS
select c.*
from ( select rowsID,studentID,years,termID,sltCourseID,sltTime,processStatus,serverNO
from tblstudentsltcourse t
where (t.years = years) and
(t.termID = termID)
order by t.sltTime ) c
where (rownum < 200)
for update;

错误是
ORA-06550: 第 2 行, 第 0 列:
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
begin case declare
exit for goto if loop mod null pragma raise return select
update while with 《an identifier》
《a double-quoted delimited-identifier》 《a bind variable》
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
《a single-quoted SQL string》 pipe

[CodeMagic]

rowsID 改为 rowid

[rabbitbug]

呵呵，rowsID是我表中的字段