| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Who called me?
A copy of this was sent to Ole C Meldahl <meldahl_at_pvv.org>
(if that email address didn't require changing)
On Tue, 22 Jun 1999 11:45:35 +0200, you wrote:
>Hi
>
>I agree, still messy but better! Not getting the parameterlist can be
>acceptable(due to overloading), but distinguishing comments from the rest is
>slightly more difficult.
>
>What about the object handle, from the stack:
>
> object line object
> handle number name
>c7f526a0 6 procedure SPORT.WHO_CALLED_ME
>c81080b8 1 anonymous block
>c81080b8 1 anonymous block
>
>Can I do a search somewhere to match this to something real?
>
Here is one solution perhaps.
I can tell you who you are (who_am_i)
I can tell you who called you (who_called_me)
I can tell you with a modified routine, who called THEM
(who_called_who_called_me)
As long as who call - who called me was a procedure, function or package (not an anonymous block) i can easily get the line of code that actually called my caller (confused -- we should be). Anyway, After installing who_called_who_called_me, we can code something like:
SQL> create or replace package demo_pkg
2 as
3 procedure proc_a( x int );
4
4 procedure proc_a( x date );
5
5 procedure proc_a( x varchar2 );
6 end;
7 /
Package created.
SQL> SQL> SQL> create or replace procedure proc_b 2 as 3 l_owner varchar2(30); 4 l_name varchar2(30); 5 l_lineno number; 6 l_caller_t varchar2(30); 7 begin 8 who_called_me( l_owner, l_name, l_lineno, l_caller_t ); 9 9 dbms_output.put_line( 'I was called by "' || l_caller_t || '" "' 10 || l_owner || '.' || 11 l_name || '"-' || l_lineno ); 12 12 who_called_who_called_me( l_owner, l_name, l_lineno, l_caller_t ); 13 dbms_output.put_line( 'they were called by "' || l_caller_t || '" "' 14 || l_owner || '.' || 15 l_name || '"-' || l_lineno );16
16 for x in ( select text from dba_source 17 where type = l_caller_t 18 and owner = l_owner 19 and name = l_name 20 and line = l_lineno ) 21 loop 22 dbms_output.put_line( x.text ); 23 end loop; 24 24 dbms_output.put_line( '--------------' );25 end;
Procedure created.
SQL> show errors
No errors.
SQL> SQL> SQL> create or replace package body demo_pkg2 as
Package body created.
SQL>
SQL> create or replace procedure proc_c
2 as
3 begin
4 demo_pkg.proc_a(1);
5 demo_pkg.proc_a(sysdate);
6 demo_pkg.proc_a('a');
7
7 demo_pkg.proc_a
8 ( 1 );
9
9 demo_pkg.
10 proc_a
11 ( 1 );
Procedure created.
So, proc_c calls proc_a. proc_a calls proc_b. Proc_B will try to tell us who called him and print out the line of code that called him (this can be useful for overloaded procedures!!)
Consider:
SQL> exec proc_c;
I was called by "PACKAGE BODY" "TKYTE.DEMO_PKG"-6
they were called by "PROCEDURE" "TKYTE.PROC_C"-4
demo_pkg.proc_a(1);
PL/SQL procedure successfully completed.
So, the first 3 calls are really nice -- we can see the inputs and everything. the 4'th call is a little obscure, we cannot tell which overloaded variant called us and the 5'th shows that if you break the packge and procedure across lines this falls apart.
Hope this helps...
create or replace procedure who_called_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;
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 4 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
-- cnt = 4 is Their Caller's Caller
if ( cnt = 4 ) 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;
>ole c
>
>Jonathan Lewis wrote:
>
>> Good point - I'd forgotten that.
>>
>> How about getting the list of function and procedure
>> names for the PACKAGE from all_source, and then
>> checking whether then function/procedure found
>> was in that list ?
>>
>> Still a bit messy, because of overloading, etc.
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site: www.jlcomp.demon.co.uk
>>
>> Ole C Meldahl wrote in message <376E145B.A82D73D_at_pvv.org>...
>> >Thanks for the input! For my purposes the body will always be visible(I
>> want
>> >to add debug-information). Searching upwards for FUNCTION/PROCEDURE will be
>> >correct in most cases, but functions within functions exists, same with
>> >PROCEDURE in strings. I would prefer to avoid complete parsing.
>
>
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
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 Tue Jun 22 1999 - 09:33:42 CDT
 |
 |