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;

SQL_TEXT

NAME

POSITION

DATATYPE_STRING

VALUE_STRING

select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is not null

:B1

 

1

TIMESTAMP

 

 

 

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;

SQL_TEXT

NAME

POSITION

DATATYPE_STRING

VALUE_STRING

ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)

 

select /* BIND_CAPTURE_TEST */ 1 from dual where :b1 is not null

:B1

 

1

TIMESTAMP

 

2014-10-16 15:04:38,234831000

 

For more information please check Doc ID 444551.1

Comments