Re: DBMS_UTILITY.FORMAT_ERROR_BACKTRACE issue

On May 1, 8:48 am, Ziad <zar..._at_gmail.com> wrote:
> Hi,
> I'm using 10g, and having a problem with backtrace when it comes to
> giving me the correct line number where the error happened. The
> problem seems to be related to for loop cursors.
> Here is my example.
> ****************************************************
> 1 CREATE OR REPLACE package za_test is
> 2
> 3 procedure test;
> 4
> 5
> 6 end za_test;
> /
>
> 1 CREATE OR REPLACE package body za_test is
> 2
> 3 procedure test is
> 4
> 5 cursor c is
> 6 select 1
> 7 from dual;
> 8
> 9 v_1 varchar2(1);
> 10
> 11 begin
> 12
> 13 for r in c loop
> 14
> 15 v_1 := '3333333';
> 16 end loop;
> 17
> 18 exception when others then
> 19 dbms_output.put_line('sqlerrm: '||SQLERRM);
> 20 dbms_output.put_line('backtrace: '||
> DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
> 21
> 22 end test;
> 23
> 24 end za_test;
> /
>
> In this example, the error is at line 15, but when I execute the
> procedure, I get:
> sqlerrm: ORA-06502: PL/SQL: numeric or value error: character string
> buffer too
> small
> backtrace: ORA-06512: at "CLAIM.ZA_TEST", line 13
>
> It points to line 13, which is the cursor for loop.
>
> If I change the procedure to use open fetch loop, I get the correct
> line number.
>
> 1 CREATE OR REPLACE package body za_test is
> 2
> 3 procedure test is
> 4
> 5 cursor c is
> 6 select 1
> 7 from dual;
> 8
> 9 v_1 varchar2(1);
> 10 r varchar2(1);
> 11 begin
> 12
> 13 open c;
> 14 loop
> 15 fetch c into r;
> 16 exit when c%NOTFOUND;
> 17 v_1 := '3333333';
> 18 end loop;
> 19 close c;
> 20
> 21 exception when others then
> 22 dbms_output.put_line('sqlerrm: '||SQLERRM);
> 23 dbms_output.put_line('backtrace: '||
> DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
> 24
> 25 end test;
> 26
> 27 end za_test;
> /
> In this case, this is the error message:
> sqlerrm: ORA-06502: PL/SQL: numeric or value error: character string
> buffer too
> small
> backtrace: ORA-06512: at "CLAIM.ZA_TEST", line 17
>
> which points to the exact line number.
> Is this a bug or by design?
> Thanks

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE shows the trace back to the last RAISE in your session. In the first case it's the for loop raising the exception; in the second case it's the assignment. The utility is working as expected.

David Fitzjarrell Received on Tue May 01 2007 - 10:18:51 CDT