oracle@rac02:/home/oracle> oerr ora 1002
01002, 00000, “fetch out of sequence”
// *Cause: This error means that a fetch has been attempted from a cursor
// which is no longer valid. Note that a PL/SQL cursor loop
// implicitly does fetches, and thus may also cause this error.
// There are a number of possible causes for this error, including:
// 1) Fetching from a cursor after the last row has been retrieved
// and the ORA-1403 error returned.
// 2) If the cursor has been opened with the FOR UPDATE clause,
// fetching after a COMMIT has been issued will return the error.
// 3) Rebinding any placeholders in the SQL statement, then issuing
// a fetch before reexecuting the statement.
// *Action: 1) Do not issue a fetch statement after the last row has been
// retrieved - there are no more rows to fetch.
// 2) Do not issue a COMMIT inside a fetch loop for a cursor
// that has been opened FOR UPDATE.
// 3) Reexecute the statement after rebinding, then attempt to
// fetch again.
–针对2)测试
drop table dbmt.test1002;
create table dbmt.test1002(id number,c varchar2(100));
insert into dbmt.test1002 select rownum,‘test’ from dual connect by rownum<10;
commit;
begin
for r in (select id from dbmt.test1002 where id<10 for update)
loop
update dbmt.test1002 set c=‘test2’ where id=r.id;
commit;
end loop;
end;
/
SQL> begin
2 for r in (select id from dbmt.test1002 where id<10 for update)---- for update
3 loop
4 update dbmt.test1002 set c=‘test2’ where id=r.id;—这不是重点，没有这个dml也不影响测试
5 commit; —commit后cursor会失效
6 end loop;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 2
ORA-06512: at line 2
