ORA-01002
reproduct ORA-01002
create table
create table test as select object_id,object_name from dba_objects where rownum <= 100;
execute script
declare
cursor v_cursor is select * from test for update;
begin
for v in v_cursor
loop
update test set object_id = 0 where current of v_cursor;
commit;
end loop;
end;
/
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 4
ORA-01002: 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:
- Fetching from a cursor after the last row has been retrieved and the ORA-1403 error returned.
- If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued will return the error.
- Rebinding any placeholders in the SQL statement, then issuing a fetch before reexecuting the statement.
Action:
- Do not issue a fetch statement after the last row has been retrieved - there are no more rows to fetch.
- Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.
- Reexecute the statement after rebinding, then attempt to fetch again.
Reference
- Oracle Database Error Message
blog comments powered by Disqus
Published
29 August 2013