Saturday, November 19, 2016

Example for DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in 11g















Often, it’s a head ache for me to debug the unhandled exception in my programs. Now, I have a pill for it. It’s DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Let’s see how it’s useful for PLSQL developers. 

Test Package:
CREATE OR replace PACKAGE test_oracle
IS
  PROCEDURE main;
END test_oracle;

/
CREATE OR replace PACKAGE BODY test_oracle
IS
  PROCEDURE Fun5
  IS
    ln_dummy_number NUMBER;
  BEGIN
      ln_dummy_number := 1 / 0; -- Kept to raise an exception
  END;
  PROCEDURE Fun4
  IS
  BEGIN
      fun5;
  END;
  PROCEDURE Fun3
  IS
  BEGIN
      fun4;
  END;
  PROCEDURE Fun2
  IS
  BEGIN
      fun3;
  END;
  PROCEDURE Main
  IS
  BEGIN
      fun2;
  EXCEPTION
    WHEN OTHERS THEN
               dbms_output.Put_line('Format error stack:'
                                    ||dbms_utility.Format_error_stack());

               dbms_output.Put_line('Print error stack:'
                                    ||Chr(10)
                                    ||dbms_utility.Format_error_backtrace());
  END;
END test_oracle;

/
show errors;
Testing:


Now, I know the exact line which caused the error. It mentioned standard oracle utilities gaves the complete back trace. It’s a nice utility!!

Hopefully it helps!!

1 Responses to “Example for DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in 11g”

Anonymous said...
October 30, 2022 at 3:44 AM

Thank you!!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.