Re: Getting Procedure/Package names dynamically

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/23
Message-ID: <3385b508.6636522@newshost>#1/1

On Tue, 20 May 1997 14:42:48 +0000, Rob Cole <rcole_at_cellnet.co.uk> wrote:

>Hi,
>
>Anyone know if there is a function to obtain the
>name of the package and procedure that is currently
>executing. I need to report the name in all
>exception sections - would be a lot easier to
>standardise my error reporting.
>
>Regards

Here is the routine I use. You would put it in your generic error handler for exmaple. Lets say you had code like:

....
exception

   when others then

                generic_error_routine;
end;
....

procedure generic_error_routine
is

	l_owner	varchar2(35);
	l_type  varchar2(35);
    l_name  varchar2(35);
	l_lineno number;
begin
	who_called_me( l_owner, l_name, l_lineno, l_type );

	.....

end;

create or replace

procedure who_called_me( owner      out varchar2,
                         name       out varchar2,
                         lineno     out number,
                         caller_t   out varchar2 )

as

    call_stack varchar2(4096) default dbms_utility.format_call_stack;     n number;
    found_stack BOOLEAN default FALSE;

    line        varchar2(255);
    cnt         number := 0;

begin

--
    loop
        n := instr( call_stack, chr(10) );
        exit when ( cnt = 3 or n is NULL or n = 0 );
--
        line := substr( call_stack, 1, n-1 );
        call_stack := substr( call_stack, n+1 );
--
        if ( NOT found_stack ) then
            if ( line like '%handle%number%name%' ) then
                found_stack := TRUE;
            end if;
        else
            cnt := cnt + 1;
            -- cnt = 1 is ME
            -- cnt = 2 is MY Caller
            -- cnt = 3 is Their Caller
            if ( cnt = 3 ) then
                lineno := to_number(substr( line, 13, 6 ));
                line   := substr( line, 21 );
                if ( line like 'pr%' ) then
                    n := length( 'procedure ' );
                elsif ( line like 'fun%' ) then
                    n := length( 'function ' );
                elsif ( line like 'package body%' ) then
                    n := length( 'package body ' );
                elsif ( line like 'pack%' ) then
                    n := length( 'package ' );
                else
                    n := length( 'anonymous block ' );
                end if;
                caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
                line := substr( line, n );
                n := instr( line, '.' );
                owner := ltrim(rtrim(substr( line, 1, n-1 )));
                name  := ltrim(rtrim(substr( line, n+1 )));
            end if;
        end if;
    end loop;
end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/    -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Fri May 23 1997 - 00:00:00 CDT