ORA-01002: fetch out of sequence

ORA-01002: fetch out of sequence

ORA-06512: at line 33

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.

There are various reasons why the program will fail because of out of sequence. Will explain one of the reason below.

Below code will error because after insert to employee table the transaction was not explicitly ended (committed or rollback).

Hence when rollback the transaction inside the loop the loop becomes out of sequence. 

create table employee (emplid number, employee_name varchar2(40));

Sample code:

declare
    cursor c1 is select invoice_num from ap_invoices_all where rownum <110;
begin

    insert into employee values (1,'ADAM');
    insert into employee values (2,'SAM');
    -- do not commit/rollback
  
    for i in c1 loop 
    
    BEGIN
      -- write bad code to make the update statement fail
      update employee set emplid = '1B';
      exception when others then
      rollback;
    END;
     dbms_output.put_line(i.invoice_num);
    end loop;
end; 

working code
declare
    cursor c1 is select invoice_num from ap_invoices_all where rownum <110;
begin

    insert into employee values (1,'ADAM');
    insert into employee values (2,'SAM');
    COMMIT;
  
    for i in c1 loop 
    
    BEGIN
      -- write bad code to make the update statement fail
      update employee set emplid = '1B';
      exception when others then
      rollback;
    END;
     dbms_output.put_line(i.invoice_num);
    end loop;
end;