Oracle: SQLERRM and SQLCODE

In an exception handler, sqlcode returns a number that corresponds to the exception that is being handled in the exception handler.

Values of sqlcode:

• Outside of an exception handler, sqlcode always evaluates to 0.
• The value for the no data found exception is 100
• For user defined exceptions, the value is either the default 1 or the value that is associated by the exception_init pragma.
• In all other cases, the value is negative.

The function sqlerrm(errCode) returns a string that describes the error whose code is errCode.

The maximum size of the value returned is 512 bytes.

The following block prints the error message for the SQL code -1045:

begin
   dbms_output.put_line(sqlerrm(-1045));
end;
/
ORA-01045: user ... lacks CREATE SESSION privilege; logon denied

sqlerrm can be called without passing the argument. In this case, sqlerrm prints the error message that is associated with the value of sqlcode.

Thus, calling sqlerrm without argument is only useful in an exception handler.

Oracle recommends to use dbms_utility.format_error_stack rather than sqlerrm unless the forall statement with its save exceptions clause is being used.

Reading nested SQL error messages in a PL/SQL exception handler

Find Oracle error messages that match a given pattern or criteria.

Using SQLERRM to create a list of Oracle events.

Using servererror triggers to catch erroneous SQL statements.

utl_lms.get_message