Re: DBMS_APPLICATION_INFO

From: ExecMan <artmerar_at_yahoo.com>
Date: Tue, 21 Feb 2012 09:56:19 -0800 (PST)
Message-ID: <bf341dfc-9b36-4a6a-8376-bafad9f7409f_at_i18g2000yqf.googlegroups.com>

On Feb 21, 11:22 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Feb 21, 10:04 am, ExecMan <artme..._at_yahoo.com> 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:
> >    DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROCEDURE_A');
>
> > And at the end of PROCEDURE_A we have this:
>
> > DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null);
>
> > Now, say PROCEDURE_A calls PROCEDURE_B, then PROCEDURE_B has:
> >    DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROCEDURE_B');
> > .
> > .
> >    DBMS_APPLICATION_INFO.SET_CLIENT_INFO(null);
>
> > 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