Re: Obtaining error line number

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 20 May 1999 13:09:19 GMT
Message-ID: <3747093f.7936722@newshost.us.oracle.com>

A copy of this was sent to Marek Ochał <ochal_at_comarch.pl> (if that email address didn't require changing) 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)

SQL> create or replace

  2  procedure who_called_me( owner      out varchar2,
  3                           name       out varchar2,
  4                           lineno     out number,
  5                           caller_t   out varchar2 )
  6  as
  7      call_stack  varchar2(4096) default dbms_utility.format_call_stack;
  8      n           number;
  9      found_stack BOOLEAN default FALSE;
 10      line        varchar2(255);
 11      cnt         number := 0;

 12 begin
 13 --

 14      loop
 15          n := instr( call_stack, chr(10) );
 16          exit when ( cnt = 3 or n is NULL or n = 0 );
 17  --
 18          line := substr( call_stack, 1, n-1 );
 19          call_stack := substr( call_stack, n+1 );
 20  --
 21          if ( NOT found_stack ) then
 22              if ( line like '%handle%number%name%' ) then
 23                  found_stack := TRUE;
 24              end if;
 25          else
 26              cnt := cnt + 1;
 27              -- cnt = 1 is ME
 28              -- cnt = 2 is MY Caller
 29              -- cnt = 3 is Their Caller
 30              if ( cnt = 3 ) then
 31                  lineno := to_number(substr( line, 13, 6 ));
 32                  line   := substr( line, 21 );
 33                  if ( line like 'pr%' ) then
 34                      n := length( 'procedure ' );
 35                  elsif ( line like 'fun%' ) then
 36                      n := length( 'function ' );
 37                  elsif ( line like 'package body%' ) then
 38                      n := length( 'package body ' );
 39                  elsif ( line like 'pack%' ) then
 40                      n := length( 'package ' );
 41                  else
 42                      n := length( 'anonymous block ' );
 43                  end if;
 44                  caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
 45                  line := substr( line, n );
 46                  n := instr( line, '.' );
 47                  owner := ltrim(rtrim(substr( line, 1, n-1 )));
 48                  name  := ltrim(rtrim(substr( line, n+1 )));
 49              end if;
 50          end if;
 51      end loop;

 52 end;
 53 /

Procedure created.

SQL>
SQL> create or replace procedure where_am_i

  2                                             ( owner      out varchar2,
  3                           name       out varchar2,
  4                           lineno     out number,
  5                           caller_t   out varchar2 )

  6 as
  7 begin
  8 who_called_me( owner, name, lineno, caller_t );   9 end;
 10 /

Procedure created.

SQL> show errors
No errors.

SQL> 
SQL> 
SQL> drop table t;

Table dropped.

SQL> create table t ( x int, y int );

Table created.

SQL>
SQL> create or replace procedure do_an_error   2 as

  3          owner      varchar2(30);
  4          name       varchar2(30);
  5          lineno     number;
  6          caller_t   varchar2(30);
  7  begin
  8          insert into t values ( 'x', 'y' );
  9  exception
 10          when others then
 11                  where_am_i( owner, name, lineno, caller_t );
 12                  dbms_output.put_line( sqlcode );
 13                  dbms_output.put_line( sqlerrm );
 14                  dbms_output.put_line( caller_t || ' ' ||
 15                                                            owner || '.' ||
name  ||
 16                                                            '--' || lineno );

 17 end;
 18 /

Procedure created.

SQL> show errors
No errors.
SQL>
SQL> exec do_an_error
-1722
ORA-01722: invalid number
PROCEDURE TKYTE.DO_AN_ERROR--11 See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 20 1999 - 08:09:19 CDT