1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74
| PROMPT CREATE OR REPLACE PACKAGE BODY p_agent
CREATE OR REPLACE procedure qui_suis_je is
l_owner varchar2(30);
l_name varchar2(30);
l_lineno number;
l_type varchar2(30);
begin
dbms_output.put_line('je suis : ');
qui_appelle_moi( l_owner, l_name, l_lineno, l_type );
dbms_output.put_line('1 : '||l_owner);
dbms_output.put_line('2 : '||l_name);
dbms_output.put_line('3 : '||l_lineno);
dbms_output.put_line('4 : '||l_type);
end;
CREATE OR REPLACE procedure qui_appelle_moi( 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, 10, 10 ));
line := substr( line, 22 );
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 ' );
elsif ( line LIKE 'anonymous%' ) then
n := length( 'anonymous block ' );
else
n := NULL;
end IF;
IF ( n IS NOT NULL ) then
caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 ))));
else
caller_t := 'TRIGGER';
end IF;
line := substr( line, nvl(n,1) );
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; |
Partager