Definition:
In Oracle PL/SQL, SQLCODE is an error trapping function which returns a predefined error number associated with the last standard exception raised by the Oracle Server. It is a negative number except for the NO_DATA_FOUND exception, which has a SQLCODE of 100. It is defined in the Oracle STANDARD package.
If SQLCODE is invoked in execution block, its value is 0, i.e. "successful operation". For user defined exceptions the value of SQLCODE is 1 or the number which is associated with the exception in PRAGMA EXCEPTION_INIT.
Example Syntax:
SQLCODE
Note that SQLCODE is a function; therefore it must be assigned to a local variable to capture its value.
Example Usage:
The PL/SQL block below raises the ZERO_DIVIDE exception and displays the Exception Number.
DECLARE
L_NUM1 NUMBER;
L_NUM2 NUMBER;
BEGIN
L_NUM1 := 10;
L_NUM2 := 0;
DBMS_OUTPUT.PUT_LINE('RESULT:'||L_NUM1/L_NUM2);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Number:'||SQLCODE);
END;
/
Exception Number:-1476
PL/SQL procedure successfully completed.
Related Links: