Re: Executing Procedures

From: Mladen Gogala <>
Date: Wed, 15 Feb 2012 17:27:49 +0000 (UTC)
Message-ID: <jhgpul$r49$>

On Wed, 15 Feb 2012 07:59:15 -0800, Mark D Powell wrote:

> On Feb 15, 9:57 am, ExecMan <> wrote:

>> Hi,
>> We want to record into a table the owner of the procedure, who called
>> it, and the name.  So, when a procedure is executed how would I query
>> the data dictionary to retrieve this information?  I cannot find any
>> code which tells me these 3 pieces of information.
>> Regards

> The built-in audit feature can produce a record of who called what
> procedure when if you audit execute; otherwise, I believe you would need
> to modify the procedures to log usage. Auditing must be turned on in
> advance.
> You can find out within a procedure who called it via the call stack.
> Tom Kyte has published routines for getting this information:

> HTH -- Mark D Powell --

There is also a way for procedure to answer the questions like: who am I? What is my purpose? What is the answer to the question of life, universe and everything? What is an airspeed velocity of an unladen swallow? Every PL/SQL object has the global VARCHAR2 variable $$PLSQL_UNIT which will return its name:

  1 create or replace function tkyte_demo return varchar2 deterministic   2 as
  3 begin
  4 return($$PLSQL_UNIT);
  5* end;
SQL> / Function created.

SQL> select tkyte_demo from dual;


TKYTE_DEMO There is also a variable called $$PLSQL_LINE.
Received on Wed Feb 15 2012 - 11:27:49 CST

Original text of this message