PL/SQL User's Guide and Reference Release 2 (9.2) Part Number A96624-01 |
|
PL/SQL Language Elements, 49 of 52
The function SQLCODE
returns the number code associated with the most recently raised exception. SQLCODE
is meaningful only in an exception handler. Outside a handler, SQLCODE
always returns 0
.
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
unless you used the pragma EXCEPTION_INIT
to associate the exception with an Oracle error number, in which case SQLCODE
returns that error number. For more information, see "Retrieving the Error Code and Error Message: SQLCODE and SQLERRM".
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. First, you must assign the value of SQLCODE
to a local variable, as follows:
my_sqlcode := SQLCODE; ... INSERT INTO errors VALUES (my_sqlcode, ...);
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
.
In the following example, you insert the value of SQLCODE
into an audit table:
DECLARE my_sqlcode NUMBER; BEGIN ... EXCEPTION WHEN OTHERS THEN my_sqlcode := SQLCODE; INSERT INTO audits VALUES (my_sqlcode, ...); END;
|
Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|