Oracle / PLSQL: SQLCODE Function

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

What does the SQLCODE Function do?

The SQLCODE function returns the error quantity associated with the most lately raised error exception. This feature ought to only be used inside the Exception Handling section of your code:

Syntax

The syntax for the SQLCODE characteristic in Oracle/PLSQL is:

SQLCODE

Parameters or Arguments

There are no parameters or arguments for the SQLCODE function.

Note

See also the SQLERRM function.

Example

Since EXCEPTION HANDLING is usually 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 may want to use the SQLCODE feature to elevate an error as follows:

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

Or you should log the error to a table using the SQLCODE function 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;