Error Handling with Dbms_utility.format_error_backtrace

Error handling is a crucial part of writing reliable PL/SQL code. While Oracle provides exception handling mechanisms using BEGIN…EXCEPTION…END, developers often face difficulties in identifying the exact location of an error in complex procedures, functions, or triggers. Oracle’s DBMS_UTILITY.FORMAT_ERROR_BACKTRACE function is a powerful tool that helps by showing the call stack and pinpointing where the error actually occurred.

In this article, we will explore how to use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for error handling in PL/SQL, its advantages over traditional error handling, practical examples, and best practices.

What is DBMS_UTILITY.FORMAT_ERROR_BACKTRACE?

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE is a built-in Oracle function that returns a string showing the line number of the error and the call stack where the exception was raised. Unlike SQLERRM, which only provides the error message, this function traces the exact line of code that caused the error, even if it occurred deep inside nested subprograms.

Why Use FORMAT_ERROR_BACKTRACE?

When debugging PL/SQL code, standard error handling has limitations:

• SQLERRM only tells you the error message, not where it happened.
• DBMS_UTILITY.FORMAT_ERROR_STACK shows the error stack, but not the exact line number inside the PL/SQL block.
• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE solves this by providing both the subprogram call stack and the line numbers where errors occurred.

This makes it much easier to debug complex programs with multiple nested procedures and functions.

Syntax

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

• Returns: VARCHAR2
• Can be used inside an exception block to display the error trace.

Example: Basic Usage

DECLARE
   v_num NUMBER := 10;
   v_den NUMBER := 0;
   v_res NUMBER;
BEGIN
   v_res := v_num / v_den; -- Will cause division by zero
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Output:

Error: ORA-01476: divisor is equal to zero
Backtrace: ORA-06512: at line 6

This shows not only the error but also the exact line number (6) where it happened.

Example: Nested Procedures

When errors occur inside nested procedures, FORMAT_ERROR_BACKTRACE is extremely helpful.

CREATE OR REPLACE PROCEDURE inner_proc IS
   v_dummy NUMBER;
BEGIN
   v_dummy := 1 / 0; -- Error here
END;
/

CREATE OR REPLACE PROCEDURE outer_proc IS
BEGIN
   inner_proc; -- Calls inner procedure
END;
/

BEGIN
   outer_proc; -- Main block
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      DBMS_OUTPUT.PUT_LINE('Backtrace: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Output:

Error: ORA-01476: divisor is equal to zero
Backtrace: ORA-06512: at "SCHEMA.INNER_PROC", line 4
ORA-06512: at "SCHEMA.OUTER_PROC", line 3
ORA-06512: at line 2

Here, the backtrace shows the chain of calls, allowing you to track the error from the innermost procedure to the outermost block.

Difference Between FORMAT_ERROR_BACKTRACE and FORMAT_ERROR_STACK

Best Practices for Using FORMAT_ERROR_BACKTRACE

1. Always include it in exception handlers – It helps to track errors efficiently.
2. Log the backtrace into error tables – Store results in a logging table for production debugging.
3. Combine with SQLERRM – Display both the error message and backtrace for complete details.
4. Use in large applications – Especially useful for debugging multi-level procedure calls.
5. Format output neatly – Make the error logs readable for faster troubleshooting.

Example: Logging Errors into a Table

CREATE TABLE error_log (
   err_date      DATE,
   err_message   VARCHAR2(4000),
   err_backtrace VARCHAR2(4000)
);

-- 2) Anonymous block to raise and log an error
DECLARE
  v_num   NUMBER := 10;
  v_den   NUMBER := 0;
  v_res   NUMBER;

  -- Capture values in variables (PL/SQL VARCHAR2 allows up to 32767)
  v_errmsg   VARCHAR2(32767);
  v_bt       VARCHAR2(32767);
BEGIN
  -- Intentionally causes ORA-01476
  v_res := v_num / v_den;

EXCEPTION
  WHEN OTHERS THEN
    -- Capture message and backtrace first
    v_errmsg := SQLERRM;
    v_bt     := DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

    -- Insert using variables; SUBSTR to fit 4000-byte columns just in case
    INSERT INTO error_log (err_date, err_message, err_backtrace)
    VALUES (SYSDATE,
            SUBSTR(v_errmsg, 1, 4000),
            SUBSTR(v_bt,     1, 4000));

commit;
END;
/

This way, all errors are recorded with their backtrace for later analysis.

See also: Using DBMS_OUTPUT to Debug PL/SQL Code

Conclusion

Error handling in PL/SQL becomes much more effective with DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. Unlike traditional methods that only provide error messages, this function traces the exact line number and call stack where the exception occurred. By using it with SQLERRM and proper logging, you can significantly improve the debugging process and maintain more reliable applications.