引言:为了纪念将要离去的DBA,记下这个关于ORA-01002的日志。
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.
--------------------
意思就是说,如果一个游标你COMMIT了,但又试图去FETCH,那么就会出现这个错误,因为COMMIT的之后,系统认为需要FETCH的已经FETCH了,典型的是FOR UPDATE,但隐式的游标FETCH也可能导致这个问题。
举例就更清楚了。
例子1:PL/SQL DEVELOPER 7.X
第一步:提取数据
第二步骤:修改第二行中的KHRQ为3,并点击COMMIT按钮(绿色那个)
点了之后变为灰色。
第三步: 再次点击FETCH图标(倒数第二个绿色按钮),结果如下:
结论1:COMMIT之后,认为当前的需要提取的数据都提取了,还想再FETCH,则会使得指针指向错误的地方,结果异常出现。
例子二:PL/SQL BLOCK 代码
declare
r sys_refcursor;
x test%rowtype;
begin
open r for select * from test for update;
fetch r into x;
commit;
close r;
end;
运行之后,ok。
换一段代码:
declare
r sys_refcursor;
x test%rowtype;
begin
open r for select * from test for update;
fetch r into x;
commit;
fetch r into x;
close r;
end;
运行,则提示如下:
这下明白了吧?
结论:简而言之,不要在commit一个for update游标之后再fetch,