How to resolve fetch out of sequence in oracle?

Question

I have a procedure in which I am often getting the following error in oracle 11g:

ORA-01002: fetch out of sequence ORA-06512: 
at "LEAVE.GES_ERP_LEV_FFS_INTERFACE_PRC", line 350 ORA-06512: at line 1.

at line 350 I have-

BEGIN

  FOR V_INTERFACE_EMP IN CUR_INTERFACE_EMP LOOP        (Line 350)
      EXIT WHEN CUR_INTERFACE_EMP%NOTFOUND;
      V_ERR_FLAG  := 'N';
      V_LOCAL_EMP := 'Y';

      BEGIN

The Cursor CUR_INTERFACE_EMP is declared as below

SELECT GELF.*
   FROM GES_ERP_LEV_FFS_INTERFACE_T GELF
 WHERE (GELF.BALANCE_FLAG != 'W' 
         OR GELF.CASE_FLAG = 'S' 
         OR SELF.BALANCE_FLAG IS NULL)
    AND GELF.PROCESS_FLAG = 'N'
    AND GELF.DATE_OF_RELEASE <= TRUNC(SYSDATE);

If i update some records of the table with Process_Flag Y,the batch works fine for some time and then again after some days we get this same issue.

Please help,let me know in case data is also needed for the mentioned table.

Solution

If i update some records of the table with Process_Flag Y,the batch works fine for some time and then again after some days we get this same issue.

You try to fetch from a SELECT FOR UPDATE, however a COMMIT has already been issued before it.

I think you have a COMMIT somewhere INSIDE the LOOP which is causing this issue.

A quote by Tom Kyte here:

for x in ( select rowid rid, t.* from T ) loop
     update T set x = x+1 where rowid = x.rid;
     commit;
  end loop;

That implicit cursor is fetched from "across a commit". It is the practice of keeping a cursor open after committing. It is a bad practice and is a common cause of ORA-1555 (the above looping construct in particular)

Also, you are using a CURSOR FOR LOOP. The CURSOR FOR LOOP will terminate when all of the records in the cursor have been fetched. So, you don't need to EXIT explicitly.

You could simply do it as:

FOR V_INTERFACE_EMP IN CUR_INTERFACE_EMP 

   LOOP        

     V_ERR_FLAG  := 'N';
     V_LOCAL_EMP := 'Y';
     ...
   END LOOP;

Comments

[Thepallav_abhi]

Thanks for the reply Lalit, Commit is there inside the loop but in the exception part. However i am not getting this issue daily once in a month this issue comes. Thats why we are unable to track it. I would be great if you could help .

[Lalit Kumar B]

You have your answer in the asktom link I have provided in the answer.