Re: logging the input values the procedure/function is called with
- From: dd yakkali <dd.yakkali@xxxxxxxxx>
- To: Mathias Magnusson <mathias.magnusson@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 2 May 2009 01:48:28 -0400
mathias,
Thanks for the reply. I want to find out whether there is any way of getting
the input values without really passing them. Also the problem stack trace
or owa_util.who_called_me is they would not give the name of the member in a
package, it just gives me the package name. This is another thing I wanted
to findout: if the caller is a packaged function or packaged procedure, how
do i get the name in the form of package.procedure/function.
If nothing works, as you said, I thought of concatenating all the input
values as a string and pass it on to log_input_values as an argument.
Thanks
Deen
On Sat, May 2, 2009 at 1:16 AM, Mathias Magnusson <
mathias.magnusson@xxxxxxxxx> wrote:
> 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:
