Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Obtaining call stack list in PL/SQL
Jason
I agree with your comments about error handling but I think the problem may be the function dbms_utility.format_error_stack. My understanding of what happens is as follows: If you catch an exception in plsql the error stack is truncated - you lose everything appart from the first line of the error. Thus when you use the dbms_utility.format_error_stack function, it hasnt got a hope of doing the job Oracle claims it is for.
sqlplus, pro*c and even forms (remember, it has its own implementation of plsql) work because they run the plsql block in a harness and you can catch and report the error stack.
Marc,
the error stack in both your plsql examples was
----Error stack----
vx length is 18
ORA-20000: Erreur
It's been truncated and isnt of much use, apart from giving you a general
error.
I guess you cant use the call stack from a calling routine, because when you call it you arent in procedure where the error occured (hence your programmers comments, you would have to have lots of 'when others' triggers - ugly)
Another problem with relying on error stacks in general, is that cursor for loops truncate them too (I guess they have implicit 'when others' trigger). I dont them because of this problem.
The error stack of you first plsql block is misleading too
ERROR at line 1:
ORA-20000: Erreur ORA-06512: at "NTC_ADMIN2.X", line 29 ORA-06512: at "NTC_ADMIN2.X", line 4 ORA-06512: at line 2
Sorry, I dont have any easy workarounds for you. Perhaps you can write a simple pro*c (or java) harness to call all your dbms_jobs.
Regards
Oli
Jason Judge <jason.judge_at_virgin.net> wrote in message
news:7v5e9r$d05$1_at_nclient13-gui.server.virgin.net...
> If your developer needs to revisit every package and procedure to make use
> of dbms_utility.format_error_stack then he has taken a design decision at
an
> early stage that has resulted in this problem - I don't see any simple way
> around this.
>
> Since Oracle allows errors to propagate outwards to enclosing blocks, this
> feature can be made use of by allowing the errors to do just that and not
to
> try to catch them all in 'when others' triggers all over the shop. A
single
> 'when others' can catch the full stack at the top-most level - whether it
be
> a Form, Pro*C module or PL/SQL - and display the message in an appropriate
> fashion. It can also pick out text from the stack and translate it into a
> business message for the user. I have used this technique very successful
on
> a number of projects and keep well clear of 'when others' if errors should
> simply to allowed to propagate upwards to a general error handling
routine.
>
> I hope this is helpful.
>
> Regards,
>
> Jason Judge
>
>
Received on Thu Oct 28 1999 - 06:33:23 CDT