Re: Identifiying the name of the function/procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 10 Jul 1998 15:16:24 GMT
Message-ID: <35aa3026.8710014@192.86.155.100>

A copy of this was sent to pgomeza_at_my-dejanews.com (if that email address didn't require changing) On Fri, 10 Jul 1998 02:15:55 GMT, you wrote:

>Hi all,
>
>Anybody know how can I get the name of the function or procedure that
>is currently running. I mean somethin like $0 of Unix, where you can
>get the name of the command/script.
>
>All solutions accepted, like selecting a table like v$..., or others.
>

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 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;
/

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;
/

>Thanks, all
>
>Pablo Gómez Aguilera
>
>E-mail: pgomeza_at_yahoo.com
> pgomeza_at_my-dejanews.com
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

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 Fri Jul 10 1998 - 10:16:24 CDT