Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

owa_util.who_called_me

2974105Aug 19 2016 — edited Aug 22 2016

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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 19 2016
Added on Aug 19 2016
22 comments
4,766 views