V$SQL_BIND_CAPTURE

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.