How to register / track Oracle stored procedure calls with parameter values?

Question

We are looking for a way to register any call to stored procedures in Oracle and see what parameter values ​​were used for the call.

We are using Oracle 10.2.0.1

We can write SQL statements and see related variables, but when we track stored procedures, we see B1, B2, etc. binding variables, but not values.

We would like to see the same information that we saw in the MS SQL Server Profiler.

Thanks for any help

Answer 1

You can see the DBMS_APPLICATION_INFO package . This allows you to “document” your PL / SQL code with whatever information you want, but this entails adding calls to each procedure, which should be instrumental.

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

Answer 2

I think you use the word "magazine" in a weird way.

We can register SQL expressions ...

Are you sure you want to say that you can TRACE SQL statements with bind variables? Tony's answer is focused on the ability to record what you are doing. This is always superior to tracking because only you know what matters to you. Perhaps the execution of your process is highly dependent on querying the value from the table. Since this value changes and is not passed as a parameter, you may lose this information.

But if you really use what you are doing, you can include this value in your log table, and you will know not only the variables you passed in, but also the key value.

change system set events '10046 trace name context forever, level 12'; Is that what you used?

Answer 3

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

I will try to describe what we did:

Using the enterprise manager (like dbo), we switched to a session and started tracing

start tracing Enable wait information, bind information

Run an operation on our application, which falls into the database

Complete the trace, run it on the output:

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

What we want to see is "these procedures were called with these parameters." What do we get:

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

So, we can track the procedures that were called, but we do not get the actual values ​​of the parameters, just: v0, etc.

I understand that we did the same as the alter operator, but let us know if that is not the case.

thanks

Answer 4

You use 10g 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 and serial_num = SERIAL # from v $ session