The function SQLCODE
returns the number code of the most recent exception.
For internal exceptions, SQLCODE
returns the number of the associated Oracle error. The number that SQLCODE
returns is negative unless the Oracle error is no data found, in which case SQLCODE
returns +100
. For user-defined exceptions, SQLCODE
returns +1
, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT
.
sqlcode function ::=
SQLCODE
is only useful in an exception handler. Outside a handler, SQLCODE
always returns 0
. SQLCODE
is especially useful in the OTHERS
exception handler, because it lets you identify which internal exception was raised. You cannot use SQLCODE
directly in a SQL statement. Assign the value of SQLCODE
to a local variable first.
When using pragma RESTRICT_REFERENCES
to assert the purity of a stored function, you cannot specify the constraints WNPS
and RNPS
if the function calls SQLCODE
.
Example 13-6 shows the use of SQLCODE
and SQLERRM
.
Example 13-6 Using SQLCODE and SQLERRM
DECLARE name employees.last_name%TYPE; v_code NUMBER; v_errm VARCHAR2(64); BEGIN SELECT last_name INTO name FROM employees WHERE employee_id = 1000; EXCEPTION WHEN OTHERS THEN v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1 , 64); DBMS_OUTPUT.PUT_LINE('The error code is ' || v_code || '- ' || v_errm); END; /
For examples, see the following: