ORACLE v$sql_bind_capture에서 TIMESTAMP값이 안보이는 문제 workaround

오라클 11G R2 이전 버전에서는 BUG으로 인해 v$SQL_BIND_CAPTURE에서 TIMESTAMP 형태의 bind 값이 NULL로 보입니다. 그러나 ANYDATA.AccessTimestamp(value_anydata) 를 이용해 볼수 있는 workaround 가 있군요.

참고: V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP (Doc ID 444551.1)

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 name, position, datatype_string, was_captured, value_string, 
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id = 
'1mf1ch9vsr06a'; 

NAME POSITION DATATYPE_STRING WAS 
------------------------------ ---------- --------------- --- 
VALUE_STRING 
------------------------------------------------------------------------------ 
-- 
ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA) 
--------------------------------------------------------------------------- 
:B1 1 TIMESTAMP YES 
05-JUL-07 12.20.23.311417000 PM