How do I log/trace Oracle stored procedure calls with parameter values?

Question

We're looking for a way to log any call to stored procedures in Oracle, and see what parameter values were used for the call.

We're using Oracle 10.2.0.1

We can log SQL statements and see the bound variables, but when we track stored procedures we see bind variables B1, B2, etc. but no values.

We'd like to see the same kind of information we've seen in MS SQL Server Profiler.

Thanks for any help

Answer 1

You could take a look at the DBMS_APPLICATION_INFO package. This allows you to "instrument" your PL/SQL code with whatever information you want - but it does entail adding calls to each procedure to be instrumented.

See also this AskTom thread on using DBMS_APPLICATION_INFO to monitor PL/SQL.

Answer 2

I think you are using the word "log" in a strange manner.

We can log SQL Statements...

Do you really mean to say you can TRACE sql statements with bind variables? Tony's answer is directed to the ability to LOG what you are doing. This is always superior to tracing because only you know what is important to you. Perhaps the execution of your process depends heavily on querying a value from a table. Since that value changes and it's not passed in as a parameter, you could lose that information.

But if you actually LOG what you are doing, you can include that value in your Log table and you'll know not only the variables you passed in but that key value as well.

alter system set events '10046 trace name context forever, level 12'; Is that what you were using?

Answer 3

Yes, I think I should have used the term 'trace'

I'll try to describe what we've done:

Using the enterprise manager (as dbo) we've gone to a session, and started a trace

start trace Enable wait info, bind info

Run an operation on our application that hits the DB

Finish the trace, run this on the output:

tkprof .prc output2.txt sys=no record=record.txt explain=dbo@DBINST/PW

What we're wanting to see is, "these procedures were called with these parameters" What we're getting is:

Begin dbo.UPKG_PACKAGENAME.PROC(:v0, :v1, :v2 ...); End;
/
Begin dbo.UPKG_PACKAGENAME.PROC2(:v0, :v1, :v2 ...); End;
/
...

So we can trace the procedures that were called, but we don't get the actual parameter values, just the :v0, etc.

My understanding is that what we've done is the same as the alter system statement, but please let us know if that's not the case.

Thanks

Answer 4

are you using 10g let try with this exec dbms_monitor.session_trace_enable(session_id=>xxx, serial_num=>xx, waits=>true, binds=>true); you can get session_id=SID & serial_num=SERIAL# from v$session

Answer 5

Did this help your issue? I would like to see enable trace to see the parameter values being passed to the procedure. Please let me know if this can be done using Enable Trace from the application.

Thanks!