Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
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
PDF · Mobi · ePub

SQLERRM Function

The SQLERRM function returns the error message associated with an error number.

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

If a function invokes SQLERRM, and you use the RESTRICT_REFERENCES pragma to assert its purity, you cannot specify the constraints WNPS and RNPS.

Note:

DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, except when using the FORALL statement with its SAVE EXCEPTIONS clause. For more information, see Retrieving the Error Code and Error Message.

Syntax

sqlerrm_function ::=

sqlerrm_function
Description of the illustration sqlerrm_function.gif

Keyword and Parameter Descriptions

error_number

An expression whose value is an Oracle Database error number. For a list of Oracle Database error numbers, see Oracle Database Error Messages.

The default error number is the one associated with the current value of SQLCODE. Like SQLCODE, SQLERRM without error_number is useful only in an exception handler. Outside an exception handler, or if the value of error_number is zero, SQLERRM returns ORA-0000.

If the value of error_number is +100, SQLERRM returns ORA-01403.

If the value of error_number is a positive number other than +100, SQLERRM returns this message:

-error_number: non-ORACLE exception

If the value of error_number is a negative number whose absolute value is an Oracle Database error number, SQLERRM returns the error message associated with that error number. For example:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('SQLERRM(-6511): ' || TO_CHAR(SQLERRM(-6511)));
  3  END;
  4  /
SQLERRM(-6511): ORA-06511: PL/SQL: cursor already open
 
PL/SQL procedure successfully completed.
 
SQL> 

If the value of error_number is a negative number whose absolute value is not an Oracle Database error number, SQLERRM returns this message:

ORA-error_number: Message error_number not found;  product=RDBMS;
facility=ORA

For example:

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('SQLERRM(-50000): ' || TO_CHAR(SQLERRM(-50000)));
  3  END;
  4  /
SQLERRM(-50000): ORA-50000: Message 50000 not found;  product=RDBMS;
facility=ORA
 
PL/SQL procedure successfully completed.
 
SQL> 

Examples

Related Topics

See Also:

Oracle Database Error Messages for a list of Oracle Database error messages and information about them