Re: A pair of questions

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 Aug 1998 13:15:15 GMT
Message-ID: <35e9fe09.2367684@192.86.155.100>

A copy of this was sent to arthurk_at_ibm.net (if that email address didn't require changing) On 29 Aug 1998 03:28:13 GMT, you wrote:

>I've looked through the online docs, but have had no success... :-(
>

...
>Question 2:
>
>Is there a PL/SQL construct (package, procedure function or variable) from
>which I can determine the name (if any) of my PL/SQL procedure at run time?
>I know I can create a constant that contains this, but I (unfortunately) am not
>in a position to dictate this kind of standard to 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 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;  

>Thanks in advance!
>
>Kurt Arthur
>arthurk_at_ibm.net
>explicitly set a constant
>name of my
 

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 Sat Aug 29 1998 - 08:15:15 CDT