ORA-01002:Fetch out of sequence

Cause: This error is caused when commit is written inside the loop and hence executed for each record in the loop.

 
Solution: Write the commit statement outside the loop. So that it is executed once.

 

Example: XX_TEST – Table consists of two columns only ID number, Name Varchar2.

DECLARE

 

   CURSOR

   C

   IS

   SELECT ID,NAME FROM XX_TEST

   FOR UPDATE;

  

   L_RECORD XX_TEST%ROWTYPE;

  

  

BEGIN

OPEN C;

LOOP

FETCH C INTO

L_RECORD;

EXIT WHEN (C%NOTFOUND);

           

                UPDATE XX_TEST

                SET ID = 1

                WHERE CURRENT OF C;

--COMMIT;

END LOOP;

COMMIT;

END;