Skip Headers

PL/SQL User's Guide and Reference
Release 2 (9.2)

Part Number A96624-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

PL/SQL Language Elements, 49 of 52


SQLCODE Function

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

Syntax

Text description of sqlcode_function.gif follows
Text description of the illustration sqlcode_function.gif


Usage Notes

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.

Example

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;

Related Topics

Exceptions, SQLERRM Function


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback