Oracle FAQs

Oracle FAQs

After recently reading a post on Tom Kyte's blog I stumbled across an excellent resource which is quoted by another fellow reader of Tom's blog. The Oracle Co-operative FAQ was set-up by Jonathan Lewis and is an excellent resource for anyone who uses the Oracle Database on a daily basis as a developer, tuner or DBA. I have heard people claim that the Oracle Database is bloated. My standard response is normally, "What you mean to say is that it has features you have not used or don't know how to use". Taking the time to scan the FAQ index may well enlighten you on a few extra features/techniques you can add to your portfolio making you more proficient. It's certainly worth a look. Enjoy Kris

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

I would hate to think of the number of times I have been faced with an "ORA-01403 - No Data Found" or a "ORA-06502: PL/SQL: numeric or value error: character to number conversion" error and not known where its come from. In Java/C# you can simply make a call to the printStackTrace() method.In comparison I have always found debugging pl/sql a bit of a black art. I've known of DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for some time but forgot of its existence until recently where I was faced with an exception raise in a pl/sql package but couldn't locate where error originated from. Example usage is as follows:

When Others Then
          p_msg_id := 0001;
          p_msg_type := 'E';
          p_msg_text :=
              To_char (Substr (Sqlcode, 1, 20)) ||
              ' : ' || Substr (Sqlerrm, 1, 200) ||
              DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

Steven Feuerstein has a very nice article published on OTN and in Oracle Magazine (March/April 05). It can be found at http://www.oracle.com/technology/oramag/oracle/05-mar/o25plsql.html