查看时间戳类型的绑定变量

bisal

发布于 2021-09-06 15:46:39

4.7K00

代码可运行

文章被收录于专栏：bisal的个人杂货铺bisal的个人杂货铺

运行总次数：0

代码可运行

当我们需要找到某条使用绑定变量的SQL语句中具体用到的参数值时，通常会使用v$sql_bind_capture视图，如果是字符串类型的变量，直接检索即可，

select sql_id, name, position, datatype_string, last_captured, value_string 
  from v$sql_bind_capture
 where sql_id='&sql_id';

测试过程如下，

SQL> create table test(c1 varchar2(1), c2 date, c3 timestamp);
Table created.


SQL> var x1 varchar2(1)
SQL> exec :x1 := 1
PL/SQL procedure successfully completed.


SQL> select * from test where c1 = :x1;
no rows selected


SQL> set serveroutput on


SQL> exec sys.print_table('select sql_id, name, position, datatype_string, last_captured, value_string from v$sql_bind_capture where sql_id=''9acwyk91jxtm4''');
SQL_ID                        : 9acwyk91jxtm4
NAME                          : :X1
POSITION                      : 1
DATATYPE_STRING               : VARCHAR2(32)
LAST_CAPTURED                 : 20-apr-2021 21:16:13
VALUE_STRING                  : 1
-----------------
PL/SQL procedure successfully completed.

默认情况，每隔15分钟，会自动采集一次变量值，显示在这个vsql_bind_capture，这是受隐藏参数_cursor_bind_capture_interval的控制，默认取值900秒=15分钟，dba_hist_sqlbind则是从vsql_bind_capture采集的，可以检索历史绑定变量的信息，

另外从《v和v_的一些玄机》和《同名的同义词和视图解惑》我们知道在Oracle中数据字典定义的名称，还是存在玄机的，vsql_bind_capture其实是个同义词，他的基表是v_sql_bind_capture，其实是张视图，

SQL> select object_name, object_type from dba_objects where object_name in (upper('v$sql_bind_capture'),upper('v_$sql_bind_capture'),upper('o$sql_bind_capture'));
OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
V_$SQL_BIND_CAPTURE       VIEW
V$SQL_BIND_CAPTURE        SYNONYM

v_sql_bind_capture又是来自于osql_bind_capture，环环相扣，

create or replace view sys.v_$sql_bind_capture as
select "ADDRESS","HASH_VALUE","SQL_ID","CHILD_ADDRESS","CHILD_NUMBER","NAME","POSITION","DUP_POSITION","DATATYPE","DATATYPE_STRING","CHARACTER_SID","PRECISION","SCALE","MAX_LENGTH","WAS_CAPTURED","LAST_CAPTURED","VALUE_STRING","VALUE_ANYDATA" from o$sql_bind_capture;

可能有些扯远了，刚才说到了绑定变量类型值的查看，如果碰到传入参数类型为时间戳时，用如上的SQL，得到的可能就是一个空值。

P.S. 这里说的时间戳，指的是Timestamp，不是Date。

因为在sqlplus下不能定义日期类型的变量，不好模拟绑定变量的情况，

我们在Java中，模拟传入一个Timestamp类型的绑定变量的场景，

Timestamp c1 = new Timestamp(new java.util.Date().getTime());
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, name, password);
String sql = "select /*+ test */ * from test where c3 = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setTimestamp(1, c1);
ResultSet rs = ps.executeQuery();

P.S. 如果模拟的是Date类型变量，可以将上述1和6行改为如下，

java.sql.Date c1 = new java.sql.Date(new java.util.Date().getTime());
...
ps.setDate(1, c1);

此时执行v$sql_bind_capture的检索，value_string确实是空的，

SQL> exec sys.print_table('select sql_id, name, position, datatype_string, last_captured, value_string from v$sql_bind_capture where sql_id=''8d09fh0d9hf1x''');
SQL_ID                        : 8d09fh0d9hf1x
NAME                          : :1
POSITION                      : 1
DATATYPE_STRING               : TIMESTAMP
LAST_CAPTURED                 : 20-apr-2021 14:20:09
VALUE_STRING                  :
-----------------
PL/SQL procedure successfully completed.

需要在上述SQL中，增加一些条件，利用了ANYDATA的accesstimestamp函数，如下所示，才可看到Timestamp类型的绑定变量值，

SQL> 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' and sql_id='8d09fh0d9hf1x';
SQL_ID        NAME         POSITION DATATYPE_STRING MAX_LENGTH VALUE_STRING    DUMP(VALUE_ANYDATA)                                     ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
------------- ---------- ---------- --------------- ---------- --------------- ------------------------------------------------------- ---------------------------------------------------------------------------
8d09fh0d9hf1x :1                  1 TIMESTAMP               11                 Typ=58 Len=25: 128,214,252,11,0,0,0,0,112,138,241,129,1 20-APR-21 02.20.08.857000000 PM
                                                                               08,127,0,0,24,75,226,129,108,127,0,0,16

究其原因，MOS(444551.1)指出这是一个bug，在12c上才修复，

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.

因此，在12c前，要看到Timestamp类型绑定变量参数的值，就得依靠ANYDATA，我们是可以从《PL/SQL Packages and Types Reference》找到对这个ANADATA的介绍，

An ANYDATA TYPE contains an instance of a given type, plus a description of the type. In this sense, an ANYDATA is self-describing. An ANYDATA can be persistently stored in the database. Access functions are available based on SQL. These functions do not throw exceptions on type-mismatch. Instead, they return NULL if the type of the ANYDATA does not correspond to the type of access. If you wish to use only ANYDATA functions of the appropriate types returned in a query, you should use a WHERE clause which uses GETTYPENAME and choose the type you are interested in (say "SYS.NUMBER"). Each of these functions returns the value of a specified datatype inside a SYS.ANYDATA wrapper.

他包含了如下这些函数，

ANYDATA是一种Type类型，可以看下定义，但说实话，Type这种数据库对象，至少对我来说，确实很少接触，

SQL> exec sys.print_table('select * from dba_types where owner=''SYS'' and type_name=''ANYDATA''');
OWNER              : SYS
TYPE_NAME          : ANYDATA
TYPE_OID           : 00000000000000000000000000020011
TYPECODE           : ANYDATA
ATTRIBUTES         : 0
METHODS            : 95
PREDEFINED         : NO
INCOMPLETE         : NO
FINAL              : YES
INSTANTIABLE       : YES
PERSISTABLE        : YES
SUPERTYPE_OWNER    :
SUPERTYPE_NAME     :
LOCAL_ATTRIBUTES   :
LOCAL_METHODS      :
TYPEID             :
-----------------


PL/SQL procedure successfully completed.

v$sql_bind_capture(/dba_hist_sqlbind)是我们排查和绑定变量相关问题时非常重要的一种手段，例如能根据绑定变量值的类型，判断是否存在隐式转换。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2021/04/28 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql