| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Logging Pl/SQL run-time errors with source code line numbers ?
My experience with DBMS_UTILITY.FORMAT_ERROR_STACK is that it does not work properly (i.e. returns the same result as SQLERRM), when used from within the same PL/SQL call as the one causing the exception, but fine if called *afterwards* (!!??).
For example this doesn't seem to work:
--- Sample SQL*Plus output
SQL> create or replace Procedure proc_c Is
2 Begin
3 raise no_data_found;
4 End proc_c;
5 /
Procedure created.
SQL> create or replace Procedure proc_b Is
2 Begin
3 proc_c;
4 End proc_b;
5 /
Procedure created.
SQL> create or replace Procedure proc_a Is
2 Begin
3 proc_b;
4 End proc_a;
5 /
Procedure created.
SQL> Begin
2 proc_a;
3 End;
4 /
Begin
*
ERROR at line 1:
ORA-01403: no data found ORA-06512: at "SCOTT.PROC_C", line 3 ORA-06512: at "SCOTT.PROC_B", line 3 ORA-06512: at "SCOTT.PROC_A", line 3 ORA-06512: at line 2
### Good - this was what I expected.
### Now try to catch it in an exception
SQL> Begin
2 proc_a;
3 Exception When others Then
4 DBMS_Output.Put_Line(DBMS_Utility.format_error_stack);
5 End;
6 /
ORA-01403: no data found
but if encapsulated in e.g. Pro*C, then
EXEC SQL EXECUTE
proc_a;
END-EXEC;
if (sqlca.sqlcode != 0)
{
EXEC SQL EXECUTE
:my_var := DBMS_Utility.Format_Error_Stack;
END-EXEC;
Then :my_var ends up with what I expect.
I can't explain it either, but doing things in two calls to Oracle seems to
work.
A long time ago (13 Dec) I posted a similar question, but didn't get any
responses. I'll try to re-post that; one of us may get lucky and get a
response.
-- Hope this helps Karl Certified Oracle DBA Paolo Ghisotti wrote in message <6aq358$oav$1_at_news.IT.net>...Received on Sun Feb 01 1998 - 00:00:00 CST
>
>Peter Schneider wrote in message <34ce72c0.6016232_at_news.okay.net>...
>>On Tue, 27 Jan 1998 12:57:12 +0100, "Paolo Ghisotti"
>><p.ghisotti_at_barilla.it> wrote:
>>
>>>I'm trying to build a package for error logging and tracing to speed up
>>>program development in pl/sql.
>>>It would be very useful if one could log the source code line number when
a
>>>run-time error occurs. I know that I can query USER_ERRORS for compile
time
>>>errors and I wonder if something like that exists for run-time errors.
>>>
>>>
>>>
>>>Thanks in advance for any help.
>>>
>>>
>>>
>>>p.ghisotti_at_barilla.it
>>>
>>
>>Hi Paolo,
>>
>>have a look at DBMS_UTILITY.FORMAT_ERROR_STACK.
>>
>>HTH
>>Peter
>>
>>--
>>Peter Schneider
>>peter.schneider_at_okay.net
>
>
>Hi Peter,
>
>I checked DBMS_UTILITY.FORMAT_ERROR_STACK before posting my message, but it
>says nothing about source code line numbers (see example n.1).
>What I'm trying to obtain is the same information that I can obtain from
>SQLPlus if a stored procedure gets a run-time error and it doesn't trap it
>with exception handlers (see example n.2).
>
>Thanks for your help anyway and please let me know if you have other hints,
>
>Paolo Ghisotti
>p.ghisotti_at_barilla.it
>
>
>Example n. 1: trying to catch error stack with
>DBMS_UTILITY.FORMAT_ERROR_STACK and error handler
>Source Code:
>create or replace procedure testerrstack as
> Char13 varchar2(13);
>
> begin
> Char13 := '123456789012345';
> exception
> when others then
> dbms_output.put_line(dbms_utility.format_error_stack);
> end testerrstack;
>
>Output:
>SQLWKS> set serveroutput on;
>Server Output ON
>SQLWKS> execute testerrstack;
>Statement processed.
>ORA-06502: PL/SQL: numeric or value error
>
>
>Example N.2 : the same procedure as above but without error handler.
>SqlPlus show the line number where a run-time error occurs
>Source Code:
>create or replace procedure testerrstack as
> Char13 varchar2(13);
> begin
> Char13 := '123456789012345';
> end testerrstack;
>
>Output:
>SQLWKS> set serveroutput on;
>Server Output ON
>SQLWKS> execute testerrstack;
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "DPE.TESTERRSTACK", line 4
>ORA-06512: at line 2
>
>
>
 |
 |