Oracle / PLSQL: SQLERRM Function

This article is written about how to use the Oracle/PLSQL SQLERRM feature with syntax and examples.

What does the SQLERRM Function do?

The SQLERRM characteristic returns the error message related with the most recently raised error exception. This function ought to only be used within the Exception Handling section of your code.

Syntax

The syntax for the SQLERRM feature in Oracle/PLSQL is:

SQLERRM

Parameters or Arguments

There are no parameters or arguments for the SQLERRM function.

Note

See also the SQLCODE function.

Example

Since EXCEPTION HANDLING is normally written with the following syntax:

EXCEPTION
   WHEN exception_name1 THEN
      [statements]

   WHEN exception_name2 THEN
      [statements]

   WHEN exception_name_n THEN
      [statements]

   WHEN OTHERS THEN
      [statements]

END [procedure_name];

You ought to use the SQLERRM feature to increase an error as follows:

EXCEPTION
   WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Or you ought to log the error to a desk the use of the SQLERRM feature as follows:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;