Re: logging the input values the procedure/function is called with
- From: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>
- To: dd.yakkali@xxxxxxxxx
- Date: Sat, 2 May 2009 07:16:57 +0200
Deen,
Can you explain what problem(s) you've encountered doing this?
To me it sounds as if all you would need is to declare log_input_values as
an autonomous transaction (Only if storing data even when transaction rolls
back), then having an insert statement run if the debug level is set.
Of course the call to log_input_values will need to contain the values you
call with and I believe it will also need to send the name of the proc. I
don't know of a way in pl*sql to get the calling procedures name. I guess it
may be possible to get it by getting the call stack and parsing it.
What version of Oracle do you use?
If you go thought this trouble, wouldn't it make more sense to have a call
at the end so you can store start and end time, alternatively update with
the end time if you need data also for failed transactions.
Mathias
On Sat, May 2, 2009 at 12:24 AM, dd yakkali <dd.yakkali@xxxxxxxxx> wrote:
> PL/SQL gurus,
>
> Here is what I essentially want: I want to throw a tracing procedure in
> every stored PL/SQL object which writes the input values with name/value
> pairs that it is called with.
> Tracing will be turned on or off based on a global parameter.
>
> Here is an example of what
>
> create or replace procedure proc1 ( input1 in number, input2 in varchar2 )
> as
> begin
>
> log_input_values(PKG_UTILITY.DEBUG_LVL);
>
> /* actual code comes here */
> ----
> .....
> ....
> ....
>
> end;
> /
>
>
> based on the value of the PKG_UTILITY.DEBUG_LVL log_input_values will
> write the following
>
> date of execution
> name of the procedure
> input values: input1 => value of the input, input2 => value of the input2
>
>
>
> I know that I can trace the session with bind values to get the
> information, but it become a pain in the neck when we have web application
> using JDBC conneciton pools and i do not know which of the session executes
> the procedure. Ofcourse Java logging can do this before it makes a call to
> the procedure, but because of what ever reason at present I can not get them
> do this.
>
> Any help is greatly appreciated.
>
>
> Thanks
> Deen
>
Other related posts:
