Exception Trapping Functions

Exception Trapping Using: SQLCODE and SQLERRM

Oracle uses two built in functions for catching exceptions and getting its information, SQLCODE and SQLERRM.

SQLCODE: It returns the error number for the last encountered error. 
SQLERRM: It returns the actual error message of the last encountered error.

• When a SQL statement raises an exception, Oracle captures the error codes by using the SQLCODE and SQLERRM globally-defined variables.

• SQLCODE and SQLERRM can track exceptions that are handled by the OTHERS clause of the exception handler.

• SQLCODE returns the current error code from the error stack and the error message from the current error.

SQL> DECLARE
  2  V_Divident NUMBER:=5;
  3  V_Divisor NUMBER:=0;
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('Result : '|| V_Divident/V_Divisor);
  6  EXCEPTION
  7  When OTHERS Then
  8  DBMS_OUTPUT.PUT_LINE('Error Code : '||SQLCODE);
  9  DBMS_OUTPUT.PUT_LINE(SQLERRM);
 10  END;
 11  /
Error Code : -1476
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  V_Divident NUMBER:=25;
  3  V_Divisor NUMBER:=5;
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE('Result : '|| V_Divident/V_Divisor);
  6  EXCEPTION
  7  When OTHERS Then
  8  DBMS_OUTPUT.PUT_LINE('Error Code : '||SQLCODE);
  9  DBMS_OUTPUT.PUT_LINE(SQLERRM);
 10  END;
 11  /
Result : 5

PL/SQL procedure successfully completed.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.