V$SQL_BIND_CAPTURE Does Not Show the Value for Binds of Type TIMESTAMP

This is due to Bug 6156624 which is fixed in 12c. The "bug" is that binds are not converted into a string for the value_string column. Here is an example test case:

SQL> declare

bindts timestamp;

begin

bindts := systimestamp();

execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is

not null' using bindts;

execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is

not null' using bindts;

execute immediate 'select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is

not null' using bindts;

end;

/

PL/SQL procedure successfully completed.

SQL> select sql_id from v$sql where sql_fulltext like '%BIND_CAPTURE_TEST%'

and sql_fulltext not like '%xxx%' and command_type = 3;

SQL_ID

-------------

1mf1ch9vsr06a

SQL> SELECT

a.sql_text,

b.name,

b.POSITION,

b.datatype_string,

b.value_string

FROM

v$sql_bind_capture b,

v$sqlarea a

WHERE

b.sql_id = '3t7wc8aftsr0z'

AND

b.sql_id = a.sql_id;

So here the value_string for timestamp is showing null which is actually not. It can be obtained using a workaround of selecting the bind data via "ANYDATA.AccessTimestamp(value_anydata)".

SQL> SELECT

a.sql_text,

b.name,

b.POSITION,

b.datatype_string,

b.value_string,

ANYDATA.AccessTimestamp(value_anydata)

FROM

v$sql_bind_capture b,

v$sqlarea a

WHERE

b.sql_id = '3t7wc8aftsr0z '

AND

b.sql_id = a.sql_id;

For more information please check Doc ID 444551.1