29 January 2008

v$sql_bind_capture Not Quite Capturing SQL Binds

A week or so ago, I was working with a developer to find out why a SQL statement he was sending would sometimes work and sometimes return an invalid DATE format error. Part of the troubleshooting led us to examine the actual bind values being received by the server, just to make sure it was the same as being sent. I thought I was onto something when I saw that the fields of TIMESTAMP datatype actually were NULL:

SQL> select name, datatype_string, value_string
from v$sql_bind_capture
where sql_id='0wp5c2a3z82jr'
SQL> /


NAME DATATYPE_STRING VALUE_STRING
---- --------------- ------------
:1   TIMESTAMP       {NULL}
:2   VARCHAR2(32)    3WC
:3   VARCHAR2(32)    001
:4   TIMESTAMP       {NULL}
:5   NUMBER          8429721
:6   NUMBER          206


6 rows selected.


Unfortunately it was like this when the query both failed and succeeded. I banged my head against the wall for 30 more minutes before concluding that it must be a bug. Metalink agreed, and Note 444551.1 details the bug, which is not yet fixed and "should be fixed in 11.2," affecting versions 9.2 to 11.1. Seems kind of long for something like this to still be out in the open, and it's very annoying when you really want to see the value of a TIMESTAMP bind variable.