Hi,
I have a requirement to do some logic based on who called me.
I wanted to know is there a way to find the caller, if I am using execute immediate or [dbms_job.submit () this is not used in the sample code] ?
I am getting the details if I call the child procedure directly.
--Child Procedure
create or replace procedure child_proc(id number) as
owner_name VARCHAR2 (100);
caller_name VARCHAR2 (100);
line_number NUMBER;
caller_type VARCHAR2 (100);
begin
OWA_UTIL.WHO_CALLED_ME (owner_name,caller_name,line_number,caller_type);
DBMS_OUTPUT.put_line ( 'id:' || id
|| '##caller_type:' || caller_type
|| '##owner_name:' || owner_name
|| '##caller_name:' ||caller_name
|| '##line_number:' ||line_number
);
end;
/
--Parent Procedure
create or replace procedure parent_proc as
v_child_proc VARCHAR2(100) := 'begin child_proc (1); end;';
begin
execute immediate v_child_proc;
child_proc (2);
end;
/
--Test
begin
parent_proc;
end;
--Output
id:1##caller_type:ANONYMOUS BLOCK##owner_name:##caller_name:##line_number:1
id:2##caller_type:PROCEDURE##owner_name:HR##caller_name:PARENT_PROC##line_number:5