HANDLE EXCEPTIONS INSIDE CURSOR LOOP

Can we Continue Cursor Loop processing even after an Exception?

You may have faced situation where you have to make use of Cursor Loop to fetch data and you might face an Exception while Processing records from Cursor- In such cases your Program will terminate and abort the running process due to Exception. 

Now, you don’t want to abort the Processing but want to log the Errors and continue the processing with rest of the records.

We have been handling Exception at the End of PL/SQL Block. In this case, we need to create two Exception handling code, first one is to handle the error while processing records through Cursor Loop and the other one is to handle Exceptions outside the Cursor Loop.

Let’s see this with the help of below Example (Create a Log table to capture Errors):

STEP 1: CREATE a TABLE to Capture Error Logs:

CREATE TABLE ERROR_LOG

(

ERROR_CODE VARCHAR2(20),

ERROR_DESC VARCHAR2(400)

);

STEP 2: CREATE PL/SQL Block to Handle Exception inside Cursor Loop

SET SERVEROUTPUT ON;

DECLARE

CURSOR C IS

SELECT ROWNUM, EMP_NO, ENAME FROM EMPLOYEE_DETAILS;

V1 NUMBER;

V_SQLCODE VARCHAR2 (20);

V_SQLERRM VARCHAR2 (400);

BEGIN

FOR rec IN C LOOP

     BEGIN

IF rec.ROWNUM = 5 THEN

DBMS_OUTPUT.PUT_LINE('Error While Processing Record Number: '|| rec.ROWNUM);

DBMS_OUTPUT.PUT_LINE('Error Description is Captured in Table ERROR_LOG');

/*Generate an Error @ Line 5*/

V1 := 'x';

ELSE

DBMS_OUTPUT.PUT_LINE('Processing Record Number: '|| rec.ROWNUM);

                 END IF;

    EXCEPTION

         WHEN OTHERS THEN

          V_SQLCODE := SQLCODE;

          V_SQLERRM:= SQLERRM;

          INSERT INTO ERROR_LOG VALUES (V_SQLCODE, V_SQLERRM);

      END;

  END LOOP;

  COMMIT;

EXCEPTION

   WHEN OTHERS THEN

            V_SQLCODE := SQLCODE;

            V_SQLERRM := SQLERRM;

            ROLLBACK;

            INSERT INTO ERROR_LOG VALUES (V_SQLCODE, V_SQLERRM);

            Commit;

END;

Execute the above code and see how it works:

SQL> CREATE TABLE ERROR_LOG
  2  (
  3  ERROR_CODE VARCHAR2(20),
  4  ERROR_DESC VARCHAR2(400)
  5  );

Table created.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2  CURSOR C IS
  3  SELECT ROWNUM, EMP_NO, ENAME FROM EMPLOYEE_DETAILS;
  4  V1 NUMBER;
  5  V_SQLCODE VARCHAR2 (20);
  6  V_SQLERRM VARCHAR2 (400);
  7  BEGIN
  8  FOR rec IN C LOOP
  9       BEGIN
 10  IF rec.ROWNUM = 5 THEN
 11  DBMS_OUTPUT.PUT_LINE('Error While Processing Record Number: '|| rec.ROWNUM);
 12  DBMS_OUTPUT.PUT_LINE('Error Description is Captured in Table ERROR_LOG');
 13  /*Generate an Error @ Line 5*/
 14  V1 := 'x';
 15  ELSE
 16  DBMS_OUTPUT.PUT_LINE('Processing Record Number: '|| rec.ROWNUM);
 17                   END IF;
 18      EXCEPTION
 19           WHEN OTHERS THEN
 20            V_SQLCODE := SQLCODE;
 21            V_SQLERRM:= SQLERRM;
 22            INSERT INTO ERROR_LOG VALUES (V_SQLCODE, V_SQLERRM);
 23        END;
 24    END LOOP;
 25    COMMIT;
 26  EXCEPTION
 27     WHEN OTHERS THEN
 28              V_SQLCODE := SQLCODE;
 29              V_SQLERRM := SQLERRM;
 30              ROLLBACK;
 31              INSERT INTO ERROR_LOG VALUES (V_SQLCODE, V_SQLERRM);
 32              Commit;
 33  END;
 34  /
Processing Record Number: 1
Processing Record Number: 2
Processing Record Number: 3
Processing Record Number: 4
Error While Processing Record Number: 5
Error Description is Captured in Table ERROR_LOG
Processing Record Number: 6
Processing Record Number: 7
Processing Record Number: 8

PL/SQL procedure successfully completed.

SQL> SELECT * From ERROR_LOG;

ERROR_CODE
--------------------
ERROR_DESC
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-6502
ORA-06502: PL/SQL: numeric or value error: character to number conversion error