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 <=10;
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