This simple example, tested on Oracle 11.2, shows how to use V$SQL_BIND_CAPTURE to see the value of bind variables used in a WHERE clause. First I created a table called TAB1 with 1 row of data:
SQL> create table tab1 (col1 varchar2(10))
2 /
Table created.
SQL> insert into tab1 values ('ANDREW')
2 /
1 row created.
SQL> select * from tab1
2 /
COL1
----------
ANDREW
SQL>
Then I created a bind variable called VAR1. I used this to select the row of data with COL1 set to ANDREW then change it to BRIAN:
SQL> variable var1 varchar2(10)
SQL> begin
2 select 'ANDREW' into :var1 from dual;
3 update tab1 set col1 = 'BRIAN' where col1 = :var1;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL>
I checked that the update had worked:
SQL> select * from tab1
2 /
COL1
----------
BRIAN
SQL>
Then I looked in V$SQL_BIND_CAPTURE to see the value of the bind variable like this:
SQL> select
2 a.sql_fulltext, b.name, b.value_string
3 from v$sql a, v$sql_bind_capture b
4 where a.sql_id = b.sql_id
5 and a.child_address = b.child_address
6 and sql_text like 'UPDATE TAB1%'
7 /
SQL_FULLTEXT
-------------------------------------------------------
NAME
------------------------------
VALUE_STRING
-------------------------------------------------------
UPDATE TAB1 SET COL1 = 'BRIAN' WHERE COL1 = :B1
:B1
ANDREW
SQL>
N.B. This only seems to work for bind variables in WHERE clauses. I also tried to use it to look at the bind variable in the SQL below:
update tab1 set col1 = :var1
In this case, the value of the bind variable was not shown.
