Toad World® Forums

How can I see the actual VALUE not just the variable ?!


#1

Hello World,
How can I see the actual values for :B1, :B2 and so on ?!
I need to see the values and not the variables …
Is it possible ?
If so - how ?

Please see my screenshot


#2

In spotlight, I haven’t find a way for this.

But you can always query: select * from v$sql_bind_capture where sql_id = ‘<<sql_id>>’;

And also, there is similar historical view: DBA_HIST_SQLBIND

And there are some samples. Of course you can’t get all these values that have been executed (in my opinion).


#3

Hopefully I will not get a wrist slapping for this, but in case I do, apologies in advance.

Looking at the bind capture views in the database will not get you the full list of binds used in any one execution of a statement. You can set an initialisation parameter to grab bind values more frequently, but doing so will seriously kill performance on the database.

The one way to get a list of the binds is to start a trace before execution of the SQL you are interested in. You can do this the old way with “alter session set events 10046 trace name context forever, level 12” which gives you binds and waits, which are the most useful, or use DBMS_SYSTE or DBMS_MONITOR packages to do the same thing.

When the SQL is completed, turn off tracing again with “alter session set events 10046 trace name context off” or again with DBMS_SYSTEM or DBMS_MONITOR.

Now when you look at the trace file you generated, you will see exactly the bind variables used it the various executions of the statement. However, it is quite difficult in many trace files, to go from the list of bind variable per execution, back to the actual statement. So you need a tool to help you. There are two that I know of:

Toad’s Trace File Analyser which lives at Database->Diagnose->Trace File Browser. Open a trace file on your Desktop, select the SQL statement you are interested in, and on the bottom of the screen, click onto the SQL Statement tab. On the right side there’s a check box to “substitute bind values back into the SQL statement”. Tick that and for this execution of the SQL statement, you will see the binds. Unfortunately, I found a trace file that doesn’t show the binds in Toad 13.0.0.80. Hmmm. The left side of the display will show all the binds too.

The second option, and here’s where I might get a slapping, is my own TraceMiner2 utility. It’s at https://github.com/NormanDunbar/TraceMiner2 and I’ll say no more about it that the fact that it reads a trace file and creates an HTML file showing all the statements executed with the bind values substituted back into the SQL statement.

HTH