Re: DBMS_APPLICATION_INFO
Date: Tue, 21 Feb 2012 09:22:52 -0800 (PST)
Message-ID: <b860d73c-ed2e-4351-b477-a01432c114ef_at_j8g2000yqm.googlegroups.com>
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 -- Received on Tue Feb 21 2012 - 11:22:52 CST