v$sql_bind_capture与timestamp类型的绑定变量的数值

v$sql_bind_capture与timestamp类型的绑定变量的数值

    我们的一个程序(10g)存在一个隐式类型转换的问题，就是程序中使用timestmp类型，而表的定义为date类型。这样存在隐式转换。在查询v$sql_bind_capture视图时（10g),一直无法从VALUE_STRING获取相应数值，显示为NULL。实际上相关的值也保存在value_anydata中，如何中value_anydata中抽取呢？(11GR2也存在类似的问题).

google oracle anydata

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_anydat.htm

SELECT SQL_ID,NAME, POSITION, datatype_string, max_length, value_string, DUMP (value_anydata), ANYDATA.accesstimestamp (value_anydata)
  FROM v$sql_bind_capture a
 WHERE a.value_string IS NULL AND a.value_anydata IS NOT NULL AND datatype_string = 'TIMESTAMP'

SQL_ID,NAME,POSITION,DATATYPE_STRING,MAX_LENGTH,VALUE_STRING,DUMP(VALUE_ANYDATA),ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
5dfmd823r8dsp,:BEGIN_INTERVAL_TIME,4,TIMESTAMP,11,,Typ=58 Len=25: 160,1,140,10,0,0,0,0,248,153,239,49,219,42,0,0,120,40,201,49,219,42,0,0,120,2011-12-13 9:00:20.541000000
5dfmd823r8dsp,:END_INTERVAL_TIME,5,TIMESTAMP,11,,Typ=58 Len=25: 160,1,140,10,0,0,0,0,248,153,239,49,219,42,0,0,120,40,201,49,219,42,0,0,120,2011-12-13 10:00:24.082000000
42cfrr6x5t75c,:B2,3,TIMESTAMP,11,,Typ=58 Len=25: 160,1,140,10,0,0,0,0,248,153,239,49,219,42,0,0,120,40,201,49,219,42,0,0,120,2011-12-13 10:00:24.082000000

实际上如果是其他类型的数据也可以调用相应的函数实现。

例子:

1.建立测试表:
SQL> create table t1 as select rownum id  ,'test' b   from dual connect by level Table created.
SQL> create unique  index i_t1_id on t1(id);
Index created.

2.
SQL> var  a number;
SQL> exec :a := 1;
PL/SQL procedure successfully completed.

SQL> select * from t1 where id= :a;

ID           NAME
------------ ----
000001       test

SQL> @dpc
PLAN_TABLE_OUTPUT
--------------------------------------
SQL_ID  dyn7rd9t0u9n5, child number 0
-------------------------------------
select * from t1 where id= :a
Plan hash value: 1175136253
--------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows |
--------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |      1 |
|*  2 |   INDEX UNIQUE SCAN         | I_T1_ID |      1 |
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID"=:A)
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


39 rows selected.

--也许要执行多次才能capture。

3.
select NAME, POSITION, datatype_string, max_length, value_string,ANYDATA.accessnumber (value_anydata) from v$sql_bind_capture where sql_id='dyn7rd9t0u9n5';

NAME         POSITION DATATYPE_STRING                MAX_LENGTH VALUE_STRI     VA
---------- ---------- ------------------------------ ---------- ---------- ------
:A                  1 NUMBER                                 22 1               1