Descobrir qual procedure/função que está rodando dinamicamente
03/10/2007
As vezes precisamos saber em uma procedure/função “quem chamou” a rotina. Ou saber qual é a procedure ou package que está sendo executada… Esta rotina faz exatamente isto!
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 ');
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;
/
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;
/
Aí vai um exemplo:
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; SCOTT.DEMO
Comente aqui
