Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2) Part Number B14261-01 |
|
|
View PDF |
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 normal, successful completion
message. 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".
Syntax
sqlerrm function ::=
Keyword and Parameter Description
error_number
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 ORA-0000: normal, successful completion
message. Passing a positive number to SQLERRM
always returns the User-Defined Exception
message unless you pass +100
, in which case SQLERRM
returns the ORA-01403: no data found
message.
You cannot use SQLERRM
directly in a SQL statement. Assign the value of SQLERRM
to a local variable first, as shown in Example 13-6.
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
.
For examples, see the following:
Related Topics