Re: Obtaining call stack list in PL/SQL

From: Oliver Staats <ostaats_at_optusnet.com.au>
Date: Thu, 28 Oct 1999 21:33:23 +1000
Message-ID: <7v9ck0$p3d$1_at_news1.mpx.com.au>


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

Line 29 is the line of the 'raise' within your exception handler, the error stack got truncated when the exception was caught so you lost infomation as to where the error orginally occured (line 10). Im not sure if this is a bug - how should the statement 'raise' work?

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 - 13:33:23 CEST

Original text of this message