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 ::=

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