Question: how to get the call/error stack in an exception

[Claus Pedersen]

To Marco Kalter:
I have with little success tried to get access to the call stack from an exception. PLD can parse the error stack to show the source, how do you do that? If this is a business secret, I fully understand, but can you give me an hint on how to perform a similar function in PL/SQL code?

To all others:
can enyone tell me how I can get the name of my current package.method when an execption occours.

I have tried SQLERRM, dbms_utility.format_call_stack, dbms_utility.format_error_stack but with no success.

Our applicationh has it's own exception handler that we call with package and method name to get the right context, but I would like the exception handler to extract the current method and eventually line number by itself.

If I get an exception in the GUI, I get the full context:

ORA-02290: check constraint (SCOTT.EMP_VALID_JOB) violated
ORA-06512: at "SCOTT.TEST", line 35
ORA-06512: at line 7

or similar.

SQLERRM return the Oracle error like "ORA-02290: check constraint (SCOTT.EMP_VALID_JOB) violated" but I can not get the context.

Does anybody have an idea?

 

[Marco Kalter]

It's no secret. The error message that PL/SQL Developer receives from the Oracle server contains a line for each call. These lines are analyzed to produce the call stack. Exactly what you mentioned above.

The dbms_utility.format_call_stack only works at the level where it is called. Format_error_stack is not what you are looking for either.

 

[Claus Pedersen]

Thank you for your answer.

I havde found an example on Oracle Metalink that make use of dbms_utility.format_call_stack/format_error_stack: SQL: PL/SQL Call Stack Handling - Example Generic Error Handler
Unfortunalety, I can only get this example to work if the exception is raised from a database trigger, like it is done in the example. If the error is raised explicitly in a procedure, if the error is a table contraint error and all others I can think of, the example does not work.
I am back to square one

 

[ScottMattes]

I don't know of a native way, but I wrote a package that we use to record start/end of a pgm, entering/leaving proc/funcs and what table/action/note is about to be executed. In an exception it is used to display all that info back to the caller.

 

[Claus Pedersen]

Thank you Scott for your answer.

We use the same method in our application, but it seems a cumbersome road to tracing errors, since Oracle obviously knows it's context, but fails to pass it to the programmer.

 

[ScottMattes]

I agree. Just like they know what caused the exception, but they give an error msg with 2 or more possibilities instead of just the one that caused the problem.

 

[Joachim Rupik]

If you are on 10g you can use a DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to see a whole call stack.