Finding bind parameter values in queries in Oracle

While we write sql queries inside the application or use any ORM products, almost all queries are executed with bind parameters rather than giving the actual values in the query string itself. This approach has many benefits but it has certain limitations too in the case of debugging. If the logging of the application is turned off, it’s difficult to find the bind parameter passed onto the query. Hence it’s difficult to find as to what the bind values were and how the query plan was and what data did it return. (Of course a trace would help but there are simpler ways) With Oracle 10g, we can use the system tables to gather the information.

When a query gets executed, an entry is placed in the V$SQL table. For the query that you need to find the bind parameter, find the SQLID from the V$SQL table. For e.g., if the query that I’m trying to find is “select * from employee where empno = :1” . The steps to be followed are as given below

1. Identify the SQL ID by querying the V$SQL table

SELECT SQL_ID FROM V$SQL WHERE SQL_FULLTEXT LIKE ‘%employee%’ ORDER BY LAST_ACTIVE_TIME

The above select may return many records but its easy to find your SQL based on the timestamp. Also you can get the SQL ID from the AWR report in case you use it.

2. Based on the SQLID value, query the V$SQL_BIND_CAPTURE system table

SELECT POSITION, DATATYPE,VALUE_STRING, VALUE_ANYDATA FROM V$SQL_BIND_CAPTURE WHERE SQL_ID='anysqlidfromvsql'

From the above query, the position of bind parameters and its value (value_string) can be obtained. The original query with bind positions can be obtained from the V$SQL table. The sql_fulltext column has the full sql query text.

Now you have the actual query and the bind parameters too. Enjoy!