11.10 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 12-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 11-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 11-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;
/