Re: Logging Pl/SQL run-time errors with source code line numbers ?

From: Paolo Ghisotti <p.ghisotti_at_barilla.it>
Date: 1998/01/29
Message-ID: <6aq358$oav$1@news.IT.net>#1/1

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

Received on Thu Jan 29 1998 - 00:00:00 CST