Re: Stored procedure name

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/05
Message-ID: <35002680.9444220@192.86.155.100>#1/1

A copy of this was sent to Timothy McKeon <tim.mckeon_at_mci.com> (if that email address didn't require changing) On Wed, 04 Mar 1998 17:31:35 GMT, you wrote:

>From within a stored procedure is there a way to determine the name of
>stored procedure you
>are currently in? I couldn't find it in any of the DBMS packages.
>
>Any help would be appreciated.
>
>
>
>Tim

You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are, it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:

SQL> create or replace procedure demo
  2 as
  3 begin
  4 dbms_output.put_line( who_am_i );   5 end;
  6 /

Procedure created.

SQL> exec demo;
TKYTE.DEMO SQL> create or replace function who_am_i return varchar2 is

	l_owner		varchar2(30);
    l_name      varchar2(30);
    l_lineno    number;
    l_type      varchar2(30);

begin

   who_called_me( l_owner, l_name, l_lineno, l_type );    return l_owner || '.' || l_name;
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
Herndon VA
 
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Thu Mar 05 1998 - 00:00:00 CST