Retrieving Error Code and Error Message

In an exception handler, for the exception being handled:

You can retrieve the error code with the PL/SQL function SQLCODE, described in "SQLCODE Function".
You can retrieve the error message with either:
- The PL/SQL function SQLERRM, described in "SQLERRM Function"
  
  This function returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message (including the error code, nested messages, and message inserts such as table and column names).
- The package function DBMS_UTILITY.FORMAT_ERROR_STACK, described in Oracle Database PL/SQL Packages and Types Reference
  
  This function returns the full error stack, up to 2000 bytes.
Oracle recommends using DBMS_UTILITY.FORMAT_ERROR_STACK, except when using the FORALL statement with its SAVE EXCEPTIONS clause, as in Example 13-13.

A SQL statement cannot invoke SQLCODE or SQLERRM. To use their values in a SQL statement, assign them to local variables first, as in Example 12-23.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function, which displays the call stack at the point where an exception was raised, even if the subprogram is called from an exception handler in an outer scope
Oracle Database PL/SQL Packages and Types Reference for information about the UTL_CALL_STACK package, whose subprograms provide information about currently executing subprograms, including subprogram names

Example 12-23 Displaying SQLCODE and SQLERRM Values

DROP TABLE errors;
CREATE TABLE errors (
  code      NUMBER,
  message   VARCHAR2(64)
);

CREATE OR REPLACE PROCEDURE p AUTHID DEFINER AS
  name    EMPLOYEES.LAST_NAME%TYPE;
  v_code  NUMBER;
  v_errm  VARCHAR2(64);
BEGIN
  SELECT last_name INTO name
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = -1;
EXCEPTION
  WHEN OTHERS THEN
    v_code := SQLCODE;
    v_errm := SUBSTR(SQLERRM, 1, 64);
    DBMS_OUTPUT.PUT_LINE
      ('Error code ' || v_code || ': ' || v_errm);
 
    /* Invoke another procedure,
       declared with PRAGMA AUTONOMOUS_TRANSACTION,
       to insert information about errors. */
 
    INSERT INTO errors (code, message)
    VALUES (v_code, v_errm);

    RAISE;
END;
/