ORACLE ERROR – “ORA-1002 Fetch out of sequence”

I even think that this error was very funny, because in a not too distant past I knew exactly what was being done wrong and I could easily to correct the error.

However a few days ago when I got this error message in some developments of mine and I was not knowing what to do, I was stupefied with the error and i thought : “What is it?”

Below I will describe the two scenarios that rise this type of error.

1. The incorrect use of “FOR UPDATE” in a cursor and “COMMIT” statement

The execution of the code listed below is interrupted with the “ORA-1002 Fetch out of sequence” error. The “COMMIT” location and the “FOR UPDATE” statement in the cursor are not conjugates of the better way.

DECLARE
CURSOR c_getLogInfo IS
SELECT *
  FROM APPL_LOG
 WHERE TYPE_ID =1
FOR UPDATE;

lr_log_info  c_getLogInfo%ROWTYPE;
ln_count     NUMBER:=1;
BEGIN

  OPEN c_getLogInfo;
  FETCH c_getLogInfo INTO lr_log_info;
  WHILE(c_getLogInfo%FOUND) LOOP

     UPDATE APPL_LOG SET MESSAGE='Finished.'
      WHERE LOG_ID =  lr_log_info.LOG_ID
       AND TYPE_ID = lr_log_info.TYPE_ID;

      COMMIT;
      ln_count := ln_count + 1;
      FETCH c_getLogInfo INTO lr_log_info;
  END LOOP;
  CLOSE c_getLogInfo;

END;