Re: Obtaining error line number

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: Thu, 20 May 1999 19:39:43 GMT
Message-ID: <7i1odv$e4g$1@nnrp1.deja.com>

Thanks, Thomas for all the help you provide in the Oracle newsgroups!

I created a small extract of the procedures you posted to show the contents of the stack. But unfortunately, it appears that the stack does NOT give me the line number where the actual error occurred. Instead, it only gives me the line numbers where each procedure is called.

What the original poster was asking for, and what I wish Oracle would provide is a tool to grab the line number where the error really occurs, like SQL Plus returns if you do not code an exception.

Here is what I get from SQL Plus from my error procedure when it does not have an exception handler:

SQL> create or replace procedure aastack2 is   2 n0 number := 0;
  3 begin
  4 n0 := 25 / n0;
  5 --
  6 --
  7 end;
  8 /
Procedure created.
SQL> exec aastack2;
begin aastack2; end;
*
ERROR at line 1:

ORA-01476: divisor is equal to zero
ORA-06512: at "AGTMS09.AASTACK2", line 4
ORA-06512: at line 1

Note that line 4 is the actual location of the error. Now if I include an exception handler and show the stack, it does not report line 4, but only the line numbers where the procedures were called:

SQL> create or replace procedure aastack2 is   2 n0 number := 0;
  3 begin
  4 n0 := 25 / n0;
  5 --
  6 --
  7 exception

  8     when others then
  9       aashow_stack;

 10 end;
 11 /
Procedure created.
SQL> exec aastack2;
Show lines in stack:
----- PL/SQL Call Stack -----
  object line object
  handle number name

c2983ae0         2  procedure AGTMS09.AASHOW_STACK
c2554ba0         9  procedure AGTMS09.AASTACK2
c27f4290         1  anonymous block

End of 'Show_Stack'

Note that the stack reports line 9, where my aashow_stack procedure was called instead of line 4 where the error occurred.

Getting the system to report line 4 would be extremely helpful here.

Steve Cosner

P.S. Here is my AAShow_Stack procedure: create or replace procedure aashow_stack is

    call_stack varchar2(4096) default dbms_utility.format_call_stack;

    n           number;
    line        varchar2(255);

begin
  dbms_output.put_line('Show lines in stack:');   loop
    n := instr(call_stack,chr(10));
    exit when nvl(n,0)=0;
    line := substr( call_stack, 1, n-1 );     call_stack := substr( call_stack, n+1 );     dbms_output.put_line(line);
  end loop;
  dbms_output.put_line('End of ''Show_Stack'''); end;

In article <3747093f.7936722_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
> On Thu, 20 May 1999 11:41:42 +0200, you wrote:
>
> >Hello,
> >how can I obtain at runtime, number of PL/SQL procedure line where an
> >error has raised? I need something like SQLCODE or SQLERRM but
> >returning a line number.
> >Marek
>
> You can do this with the call stack. I have procedures i use
> "who_called_me" and "where_am_i" that tell you the object type
> (procedure, package, anonymous block,etc) and the
> owner/object_name as well as the linenumber. They are
> (followed by a quick demo)

<snip>

--== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.--- Received on Thu May 20 1999 - 14:39:43 CDT