DBMS_UTILITY.FORMAT_ERROR_BACKTRACE issue

From: Ziad <zarabo_at_gmail.com>
Date: 1 May 2007 06:48:10 -0700
Message-ID: <1178027288.245999.303520@l77g2000hsb.googlegroups.com>

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 Received on Tue May 01 2007 - 08:48:10 CDT