CREATE OR REPLACE PACKAGE my_utl_call_stack 
   AUTHID DEFINER 
IS 
   FUNCTION format_call_stack RETURN VARCHAR2; 
   FUNCTION format_error_stack RETURN VARCHAR2; 
   FUNCTION format_error_backtrace RETURN VARCHAR2; 
   FUNCTION backtrace_to RETURN VARCHAR2; 
END;

CREATE OR REPLACE PACKAGE BODY my_utl_call_stack
IS
   FUNCTION format_call_stack
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767)
         :=    '----- PL/SQL Call Stack -------'
            || CHR (10)
            || 'Line     Object name'
            || CHR (10)
            || '-------- ----------------------';
   BEGIN
      /* 1 is always this function, so ignore it. */
      FOR indx IN 2 .. utl_call_stack.dynamic_depth
      LOOP
         l_return :=
               l_return
            || case when l_return is not null then CHR (10) end
            || LPAD (TO_CHAR (utl_call_stack.unit_line (indx)), 8)
            || ' '
            || utl_call_stack.owner (indx)
            || '.'
            || utl_call_stack.concatenate_subprogram (
                  utl_call_stack.subprogram (indx));
      END LOOP;

      RETURN l_return;
   END;

   FUNCTION format_error_stack
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767);
   BEGIN
      FOR indx IN 1 .. utl_call_stack.error_depth
      LOOP
         l_return :=
               l_return
            || case when l_return is not null then CHR (10) end
            || 'ORA-'
            || LPAD (TO_CHAR (utl_call_stack.error_number (indx)), 5, '0')
            || ': '
            || utl_call_stack.error_msg (indx);
      END LOOP;

      RETURN l_return;
   END;

   FUNCTION format_error_backtrace
      RETURN VARCHAR2
   IS
      l_return   VARCHAR2 (32767);
   BEGIN
      FOR indx IN 1 .. utl_call_stack.backtrace_depth
      LOOP
         l_return :=
               l_return
            || case when l_return is not null then CHR (10) end
            || indx
            || ' -> '
            || utl_call_stack.backtrace_unit (indx)
            || ' - Line '
            || TO_CHAR (utl_call_stack.backtrace_line (indx));
      END LOOP;

      RETURN l_return;
   EXCEPTION
      WHEN OTHERS
      THEN
         IF SQLCODE = -64610
         THEN
            /* ORA-64610: bad depth indicator */
            RETURN l_return;
         ELSE
            RAISE;
         END IF;
   END;
   
   FUNCTION backtrace_to
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN    utl_call_stack.backtrace_unit (1)
             || ' on line '
             || utl_call_stack.backtrace_line (1);
   END;
END;

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE p1  
IS  
   PROCEDURE nested_in_p1  
   IS  
   BEGIN  
      DBMS_OUTPUT.put_line ('Call Stack from DBMS_UTILITY');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line ('Call Stack from UTL_CALL_STACK');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (my_utl_call_stack.format_call_stack);  
      RAISE NO_DATA_FOUND;  
   END;  
BEGIN  
   nested_in_p1;  
END;

CREATE OR REPLACE PACKAGE pkg
   AUTHID DEFINER
IS
   PROCEDURE p;
END;

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE p
   IS
   BEGIN
      p1;
   END;
END;

CREATE OR REPLACE PROCEDURE p2
   AUTHID DEFINER
IS
BEGIN
   pkg.p;
END;

CREATE OR REPLACE PROCEDURE p3  
   AUTHID DEFINER  
IS  
BEGIN  
   p2;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line ('-');  
  
      DBMS_OUTPUT.put_line ('Error Stack from DBMS_UTILTY');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);  
  
      DBMS_OUTPUT.put_line ('Error Stack from UTL_CALL_STACK');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (my_utl_call_stack.format_error_stack);  
        
      DBMS_OUTPUT.put_line ('-');  
        
      DBMS_OUTPUT.put_line ('Backtrace from DBMS_UTILITY');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line ('Backtrace from UTL_CALL_STACK');  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line (my_utl_call_stack.format_error_backtrace);  
      DBMS_OUTPUT.put_line ('-');  
      DBMS_OUTPUT.put_line ('Backtrace to: ' || my_utl_call_stack.backtrace_to());  
      RAISE;  
END;

BEGIN  
   p3;  
  
/* Trapping the exception because if I do not, LiveSQL will not   
   show the contents of the DBMS_OUTPUT buffer.   
*/  
  
EXCEPTION WHEN OTHERS  
   THEN  
      NULL; 
END;