Skip Headers

PL/SQL User's Guide and Reference
10g Release 1 (10.1)

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

Go to previous page
Go to next page
View PDF

SQLERRM Function

The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message normal, successful completion.

For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.

For user-defined exceptions, SQLERRM returns the message user-defined exception, unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message. For more information, see "Retrieving the Error Code and Error Message: SQLCODE and SQLERRM".


Description of sqlerrm_function.gif follows
Description of the illustration sqlerrm_function.gif

Keyword and Parameter Description


A valid Oracle error number. For a list of Oracle errors (ones prefixed by ORA-), see Oracle Database Error Messages.

Usage Notes

SQLERRM is especially useful in the OTHERS exception handler, where it lets you identify which internal exception was raised.

The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the following message:

ORA-0000: normal, successful completion

Passing a positive number to SQLERRM always returns the message

User-Defined Exception

unless you pass +100, in which case SQLERRM returns the following message:

ORA-01403: no data found

You cannot use SQLERRM directly in a SQL statement. Assign the value of SQLERRM to a local variable first:

my_sqlerrm := SQLERRM;
INSERT INTO errors VALUES (my_sqlerrm, ...);

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


The following example retrieves the error message associated with an unhandled exception, and stores it in an audit table. The SUBSTR function truncates the message if it is too long to fit in the table.

CREATE TABLE errors (code NUMBER, message VARCHAR2(128), happened TIMESTAMP);
   name employees.last_name%TYPE;
   my_code NUMBER;
   my_errm VARCHAR2(32000);
   SELECT last_name INTO name FROM employees WHERE employee_id = -1;
         my_code := SQLCODE;
         my_errm := SQLERRM;
         dbms_output.put_line('Error code ' || my_code || ': ' || my_errm);
-- Normally we would call another procedure, declared with PRAGMA
-- AUTONOMOUS_TRANSACTION, to insert information about errors.
         INSERT INTO errors VALUES (my_code, my_errm, SYSTIMESTAMP);
DROP TABLE errors;

Related Topics

Exceptions, SQLCODE Function