How to Fix ORA-01002: Fetch Out of Sequence in Oracle

ORA-01002 means a program fetched from a cursor that can no longer be fetched from. In PL/SQL the usual cause is a COMMIT inside a loop over a SELECT ... FOR UPDATE cursor: the commit releases the cursor's row locks, and the next fetch fails. Other causes are fetching after the last row or after the cursor was closed in client code. Commit after the loop, or loop without FOR UPDATE.

Reproduce the Error

Example:

create table demo_queue (id number, done varchar2(1));
insert into demo_queue select level, 'N' from dual connect by level <= 3;
commit;
begin
  for r in (select id from demo_queue for update) loop
    update demo_queue set done = 'Y' where id = r.id;
    commit;                                   -- releases the locks of the FOR UPDATE cursor
  end loop;
end;
/

Output:

Table DEMO_QUEUE created.

3 rows inserted.

Commit complete.

begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 2
ORA-06512: at line 2

The first row is updated and committed; the fetch of the second row then fails.

Fix: Commit After the Loop

Example:

begin
  for r in (select id from demo_queue for update) loop
    update demo_queue set done = 'Y' where id = r.id;
  end loop;
  commit;                                     -- once, after the loop
end;
/
select * from demo_queue order by id;

Output:

PL/SQL procedure successfully completed.

   ID DONE
_____ _______
    1 Y
    2 Y
    3 Y

Other Fixes

• When the rows need no lock, drop FOR UPDATE; a normal cursor survives commits inside the loop.
• For large batches, a single UPDATE statement is faster than a loop: update demo_queue set done = 'Y' where done = 'N'.
• To commit in chunks, open the FOR UPDATE cursor again for each chunk, for example with WHERE ROWNUM <= n and SKIP LOCKED; FETCH FIRST cannot be combined with FOR UPDATE and raises ORA-02014.

Things to Know

• WHERE CURRENT OF needs a FOR UPDATE cursor, so it cannot be combined with commits in the loop.
• In client programs, ORA-01002 also comes from calling fetch again after the last row was returned.

Related Guides

• How to Lock Rows with SELECT FOR UPDATE in Oracle
• How to Fetch Data from Cursor in Oracle Using For Loop

Conclusion

ORA-01002 in PL/SQL usually means a COMMIT ended the locks of a FOR UPDATE cursor that the loop was still reading. Commit after the loop, drop FOR UPDATE when no lock is needed, or replace the loop with one UPDATE.