Skip Headers
Oracle® Database PL/SQL User's Guide and Reference
10g Release 2 (10.2)

Part Number B14261-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

SQLCODE Function

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.

Syntax

sqlcode function ::=

Description of sqlcode_function.gif follows
Description of the illustration sqlcode_function.gif

Usage Notes

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.

Examples

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:


Example 10-11, "Displaying SQLCODE and SQLERRM"

Related Topics


"Exception Definition"
"SQLERRM Function"
"Retrieving the Error Code and Error Message: SQLCODE and SQLERRM"