This script and article is courtesy of asktom
Many times we are asked "in a procedure/function, can I find out who called me" or "can I dynamically figure out the name of the procedure or package that is currently executing".
You can find it in the call stack returned by dbms_utility.format_call_stack. I wrote a small routine called who_called_me that returns this sort of information (it doesn't tell you who you are; it lets you know who called you). If you wrap who_called_me with a function who_am_i, you'll get what you need. If you create the who_called_me/who_am_i routines, you'll be able to:
SQL> create or replace procedure demo
4 dbms_output.put_line( who_am_i );
SQL> exec demo;
You can download who_called_me and who_am_I from here