Using FORMAT_ERROR_BACKTRACE for Better Exception Handling

The Problem With Traditional Exception Handling

Every PL/SQL developer has encountered this frustration: an exception fires, you catch it in your handler, but the error message tells you what went wrong without telling you where it went wrong. The standard SQLERRM function returns the error message, and SQLCODE returns the error number, but neither reveals which line of code triggered the problem. In a procedure with 500 lines of logic, knowing that you got an “ORA-01403: no data found” error is only marginally helpful.

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to the Rescue

Oracle introduced DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in Oracle 10g Release 2 to solve exactly this problem. This function returns a formatted string showing the complete call stack at the point where the exception was raised, including package names, procedure names, and most importantly, line numbers.

CREATE OR REPLACE PROCEDURE process_order(p_order_id IN NUMBER)
IS
   l_customer_name VARCHAR2(100);
   l_total         NUMBER;
BEGIN
   SELECT customer_name
     INTO l_customer_name
     FROM customers
    WHERE customer_id = (
       SELECT customer_id FROM orders WHERE order_id = p_order_id
    );

   l_total := calculate_order_total(p_order_id);

   UPDATE orders
      SET status = 'PROCESSED',
          total_amount = l_total
    WHERE order_id = p_order_id;

EXCEPTION
   WHEN OTHERS THEN
      -- Log the full backtrace so we know exactly which line failed
      log_error(
         p_error_code    => SQLCODE,
         p_error_message => SQLERRM,
         p_backtrace     => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
         p_call_stack    => DBMS_UTILITY.FORMAT_CALL_STACK
      );
      RAISE;
END process_order;
/

Understanding the Output

The backtrace output looks something like this:

ORA-06512: at "HR.PROCESS_ORDER", line 7
ORA-06512: at "HR.MAIN_BATCH", line 42
ORA-06512: at line 3

This tells you the exception originated at line 7 of PROCESS_ORDER, which was called from line 42 of MAIN_BATCH. You can pinpoint the exact SELECT statement that raised the NO_DATA_FOUND error without any guesswork.

Building a Reusable Error Logging Framework

The best practice is to create a centralized error logging procedure that captures all available diagnostic information:

CREATE OR REPLACE PROCEDURE log_error(
   p_error_code    IN NUMBER   DEFAULT SQLCODE,
   p_error_message IN VARCHAR2 DEFAULT SQLERRM,
   p_backtrace     IN VARCHAR2 DEFAULT DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,
   p_call_stack    IN VARCHAR2 DEFAULT DBMS_UTILITY.FORMAT_CALL_STACK,
   p_context       IN VARCHAR2 DEFAULT NULL
)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO error_log(
      log_id, error_code, error_message,
      backtrace, call_stack, context_info,
      created_by, created_date
   ) VALUES (
      error_log_seq.NEXTVAL, p_error_code, p_error_message,
      p_backtrace, p_call_stack, p_context,
      SYS_CONTEXT('USERENV','SESSION_USER'), SYSTIMESTAMP
   );
   COMMIT;
END log_error;
/

The PRAGMA AUTONOMOUS_TRANSACTION ensures the error log entry is committed even if the calling transaction is rolled back. This is essential because most exception handlers re raise the exception or allow it to propagate, which typically leads to a rollback of the main transaction.

FORMAT_ERROR_BACKTRACE vs FORMAT_CALL_STACK

These two functions serve different purposes. FORMAT_ERROR_BACKTRACE shows the path from where the exception was raised to where it was caught. FORMAT_CALL_STACK shows the current call hierarchy at the point where the function is invoked. Together, they give you a complete picture of both what went wrong and how the code reached that point.

Version Notes

In Oracle 12c, the UTL_CALL_STACK package was introduced as a more structured alternative to the DBMS_UTILITY functions. It provides individual functions to retrieve unit names, line numbers, and error depths as separate values rather than a single formatted string. This makes it easier to parse and store diagnostic information programmatically. However, FORMAT_ERROR_BACKTRACE remains widely used because it is simpler for basic logging needs and is available in all supported Oracle versions from 10gR2 onward through 23ai.