Oracle: Advanced Error Messages

This is not an APEX specific post, however it can be useful for error handling.

A colleague showed me a great way to get more useful debug information. Normally I used SQLERRM and SQLCODE in an exception to display or store error messages. Using DBMS_UTILITY you can get more detailed Oracle error messages. Here's an example:

-- I put this in a package for demo purposes
CREATE OR REPLACE PACKAGE pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  );
END pkg_err_test;

CREATE OR REPLACE PACKAGE BODY pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  )
  AS
    v_ename emp.ename%TYPE;
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = p_empno;

    DBMS_OUTPUT.put_line ('Employee name is: ' || v_ename);
  EXCEPTION
    WHEN OTHERS THEN
      -- Basic Error Message
      DBMS_OUTPUT.put_line ('Old Error Message: ' || SUBSTR (SQLERRM, 1, 255));
      DBMS_OUTPUT.put_line ('Old Err Code: ' || SQLCODE);
      -- Advanced Error Messages
      DBMS_OUTPUT.put_line ('-- New Error Messages --');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   -- Error Message
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   -- Where it occurred
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);   -- Call Stack
  END sp_err_test;
END pkg_err_test;

-- Run the error test with an invalid employee number so an exception will be raised
EXEC pkg_err_test.sp_err_test(p_empno => 123);

DBMS Output:

Old Error Message: ORA-01403: no data found
Old Err Code: 100
-- New Error Messages --
ORA-01403: no data found

ORA-06512: at "GIFFY.PKG_ERR_TEST", line 9

----- PL/SQL Call Stack -----
object line object
handle number name
362D7814 24 package body GIFFY.PKG_ERR_TEST
362D70E0 1 anonymous block

The error message is displayed as well as where the error occurred and the call stack. In large systems this can be very helpful. You should be aware that when called from a package, it does not list the procedure or function (as seen in this example) where the error occurred so you may need to hard code the function or procedure name in your error message.