How to get the stacktrace when an exception is raised in PL/SQL (use of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE)

This article describes the use of function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE which can be used to identify the exact line where an exception has occurred.

Note that the output of this function does not contain the actual error.

Therefore, when you are trying to log the details of the error, you have to log both sqlerrm and the output of this function to get the complete picture.

Example:

create or replace package body my_pack as
   procedure a as
   begin
      -- DO something that throws an exception
      ...
   end;

   procedure b as
   begin
      -- Call procedure a
      a;
   exception
   when others then
       -- Procedure a has thrown an exception
       DBMS_OUTPUT.PUT_LINE('An exception has occurred: ' || substr(sqlerrm, 1, 512));
       DBMS_OUTPUT.PUT_LINE('The trace of the error was: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
   end;
end;
/