From: ExecMan <>
Date: Tue, 21 Feb 2012 09:56:19 -0800 (PST)
Message-ID: <>

On Feb 21, 11:22 am, Mark D Powell <> wrote:
> On Feb 21, 10:04 am, ExecMan <> wrote:
> > Hi,
> > We are using DBMS_APPLICATION_INFO to help track which procedures are
> > running.  However, one issue we are having is when procedures call
> > other procedures.
> > For example, at the top of PROCEDURE_A we have this:
> > And at the end of PROCEDURE_A we have this:
> > Now, say PROCEDURE_A calls PROCEDURE_B, then PROCEDURE_B has:
> > .
> > .
> > That wipes out the value and when control is returned to PROCEDURE_A,
> > there is no value set anymore.  So, this is great for a single
> > procedure, but how can one integrate nested procedure calls and being
> > able to use DBMS_APPLICATION_INFO more dynamically?
> > Any thoughts or suggestions?
> If you are just trying to have dbms_application_info set the current
> procedure wouldn't all you need to do is place another call to
> set_client_info after the call to procedure B in procedure A so upon
> return procedure A re-establishes itself as the runing procedure?
> That is, if procedure A is not immediately then calling procedure C.
> Do you really want to use dbms_application_info to identify what is
> running down to this level?  I can understand identifying modules or
> major features/tasks but I am not sure that identifying individual
> procedures is worth much.  It would depend on your application and how
> long the procedure runs.  It the procedure runs in under a second then
> adding the calls to set and unset the client info seems like too much
> overhead for no real value.
> Within a procedure it call dump the call stack trace to find out the
> name of the calling module.
> HTH -- Mark D Powell --

I was beginning to look at that also. I want to be able to know what is executing and who made what changes, etc. For auditing and logging.

Looking at the famous WHO_CALLED_ME or WHO_AM_I routines, I'll need to see how I can modify it to tell me now just the package name, but the procedure within the package. I think the call stack trace is the better answer too. Received on Tue Feb 21 2012 - 11:56:19 CST

Original text of this message