PL/SQL User's Guide and Reference

Contents Index Home Previous Next

SQLCODE Function

Description

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 zero.

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 "Using SQLCODE and SQLERRM" [*].

Syntax

sqlcode_function ::=

SQLCODE

Usage Notes

You cannot use SQLCODE directly in a SQL statement. For example, the following statement is illegal:

INSERT INTO errors VALUES (SQLCODE, ...);

Instead, you must assign the value of SQLCODE to a local variable, then use the variable in the SQL statement, as follows:

DECLARE
   my_sqlcode  NUMBER;
BEGIN
   ...
EXCEPTION
   ...
   WHEN OTHERS THEN
      my_sqlcode := SQLCODE;
      INSERT INTO errors VALUES (my_sqlcode, ...);
END;

SQLCODE is especially useful in the OTHERS exception handler because it lets you identify which internal exception was raised.

Related Topics

Exceptions, SQLERRM Function

Contents Index Home Previous Next