PL/SQL User's Guide and Reference

SQLERRM Function

Description

The function SQLERRM returns the error message associated with its error-number argument or, if the argument is omitted, with the current value of SQLCODE. SQLERRM with no argument is meaningful 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 "Using SQLCODE and SQLERRM" .

Syntax

sqlerrm_function ::=

SQLERRM [(error_number)]

Keyword and Parameter Description

error_number

This must be a valid Oracle error number. For a list of Oracle errors, see Oracle7 Server Messages.

Usage Notes

You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. 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. For example, the following statement is illegal:

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

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

DECLARE
   my_sqlerrm  CHAR(150);
   ...
BEGIN
   ...
EXCEPTION
   ...
   WHEN OTHERS THEN
   my_sqlerrm := SUBSTR(SQLERRM, 1, 150);
   INSERT INTO errors VALUES (my_sqlerrm, ...);
END;