RE: logging the input values the procedure/function is called with

From: Kenneth Naim <kennaim_at_gmail.com>
Date: Sat, 2 May 2009 03:27:19 -0400
Message-ID: <034c01c9caf7$6ee58c00$4cb0a400$_at_com>

How about using dbms_profiler instead of your function. It should meet most of your requirements if not all of them.  

Ken  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mathias Magnusson Sent: Saturday, May 02, 2009 3:16 AM
To: dd yakkali
Cc: oracle-l_at_freelists.org
Subject: Re: logging the input values the procedure/function is called with  

Again, what version?  

You may be able to use v$sql_bind_capture (I think that's the name, it is something like it) to get the bind variables. You may also be able to get the full procedure name from v$sqlarea as you will be able to search just for SQL executed by the process. Maybe you can use open cursors or some such to get the sql most recently executed. Still, why not just pass in the name of the procedure as a string? If you have to add the call to this procedure, you may as well add a static variable holding the name of the procedure. (Assuming noone knows of a way to get the full name.  

Do you have to get all of these calls or would sampling be enough? Then it could be possible to not add any code to any functions or procedures, but rather just find the calls in v$sqlarea and binds for them in v$sql_bind_capture. Of course there will be a chance that they age out so you miss some. Depending on why you need this, it may or may not be a feasible solution.  

Still reading constantly for v$ will most likely significantly increase your latching, so be careful...  

For some procedures and functions, this storing of the parameters may become a significant part of their elapsed time, so that is another thing to be careful with if you have code that is executed very often.  

Mathias

On Sat, May 2, 2009 at 7:48 AM, dd yakkali <dd.yakkali_at_gmail.com> wrote:

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_at_gmail.com> 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_at_gmail.com> 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      

--
http://www.freelists.org/webpage/oracle-l

Received on Sat May 02 2009 - 02:27:19 CDT