Home » SQL & PL/SQL » SQL & PL/SQL » Error logging SQLERRM, FORMAT_ERROR_STACK (Oracle 9.2)
Error logging SQLERRM, FORMAT_ERROR_STACK [message #428999] Sat, 31 October 2009 22:08 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
I am trying to improve the existing error log procedure.

Currently my environment/production system is logging SQLERRM and SQLCODE in errors.

By trying a few cases DBMS_UTILITY.FORMAT_ERROR_STACK seems a great improvement.

(In ORacle 10 seems too easy to debug errors: DBMS_UTILITY.format_error_backtrace includes line number of error.
Along with compile time line number and code unit name in a call to a log procedure/package seems like this would make debugging very easy. (Finding place of error)) I work with thousands of lines of pl/sql in packages/procedures.

My question is DBMS_UTILITY.FORMAT_ERROR_STACK seems to miss part of the error stack. Is there any other choice?


in SQLplus error reported as:
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04064: not executed, invalidated procedure "MONITOR.TEST_DEP"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "MONITOR.PCK4", line 10
ORA-06512: at line 1

In log table error inserted as:
ORA-04064: not executed, invalidated procedure "MONITOR.TEST_DEP"
ORA-06508: PL/SQL: could not find program unit being called
Error -6508: ORA-06508: PL/SQL: could not find program unit being called

First error line seems missing.

I was reproducing a object does not exist becuase of a dependency issue.

Thanks,
goo


Seems unimportant in debugging to me: DBMS_UTILITY.FORMAT_CALL_STACK



Re: Error logging SQLERRM, FORMAT_ERROR_STACK [message #429000 is a reply to message #428999] Sat, 31 October 2009 22:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
04068, 00000, "existing state of packages%s%s%s has been discarded"
// *Cause:  One of errors 4060 - 4067 when attempt to execute a stored
//          procedure.
// *Action: Try again after proper re-initialization of any application's
//          state.


This error is duly underwhelming
ORA-04064 is the important error
Re: Error logging SQLERRM, FORMAT_ERROR_STACK [message #429001 is a reply to message #428999] Sat, 31 October 2009 22:39 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
First error line seems missing

Because of scope difference.
The utility is called from inside of the package/procedure, it has no information how will the caller treat it. The missing lines are written by SQL*Plus outside the procedure.
You may try to add the utility into this/every PL/SQL block. But, do you really need that info?

[Edit: corrected the middle paragraph]

[Updated on: Sat, 31 October 2009 22:44]

Report message to a moderator

Re: Error logging SQLERRM, FORMAT_ERROR_STACK [message #429003 is a reply to message #428999] Sat, 31 October 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I am trying to improve the existing error log procedure.
Rather than improving error logging in Production code,
time & effort should be spent in QA so errors don't reach Production.
Re: Error logging SQLERRM, FORMAT_ERROR_STACK [message #429006 is a reply to message #429003] Sat, 31 October 2009 23:07 Go to previous messageGo to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
I did not think of scope, much thanks.

BlackSwan wrote on Sat, 31 October 2009 22:47
>I am trying to improve the existing error log procedure.
Rather than improving error logging in Production code,
time & effort should be spent in QA so errors don't reach Production.

Lol. Oh, do I have stories. I have been here for 1 year and there is a package that has reported an error every day since I've been here that no one has fixed. I am fixing errors in production while doing other stuff (finally got all the passwords and feel comfortable with existing system and making siginificant changes in production).

For the new stuff that I am writing for a new project, I am the only one that will look at the code. They recently tried having someone else test your code, but it is somewhat of a joke. This is a data warehouse/ used mainly for reports. When there is a problem, they just reload the day.

My favorite was calculating rolling averages to load missing financial data that goes on the general ledger. I was asked for the rolling average to be automatically calculated when the data was bad. I said no.
Oh and there is plenty more.
Good times.
Re: Error logging SQLERRM, FORMAT_ERROR_STACK [message #429007 is a reply to message #429006] Sat, 31 October 2009 23:33 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I am glad you have perspective (& a sense of humor).
Previous Topic: Create a user and give full previlages only for that schema
Next Topic: oracle 10g
Goto Forum:
  


Current Time: Fri Dec 09 15:12:40 CST 2016

Total time taken to generate the page: 0.43204 seconds