oracle的timestamp字段类型绑定变量值不显示

Cheryfs_Gaolu

于 2023-02-22 14:07:50 发布

阅读量235

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/m0_37917722/article/details/129158604

版权

问题现象

准备查看sql_id为dnmvngjb2bd2k的绑定变量值

SQL> select position,datatype_string,value_string,was_captured,LAST_CAPTURED from v$sql_bind_capture where sql_id = 'dnmvngjb2bd2k' order by POSITION,LAST_CAPTURED;

  POSITION DATATYPE_STRING                                              VALUE_STRING                                                           WAS LAST_CAPTURED
---------- ------------------------------------------------------------ ---------------------------------------------------------------------- --- ------------------
         1 VARCHAR2(128)                                                                                                                       NO
         1 VARCHAR2(128)                                                                                                                       NO
         2 TIMESTAMP                                                                                                                           YES 07-NOV-22
         2 TIMESTAMP                                                                                                                           YES 04-JAN-23

分析

was_captured显示yes表明已经捕获，但是查询没有显示。需要特殊处理转换。

解决方案

select name, position, datatype_string, was_captured, value_string,
anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id ='dnmvngjb2bd2k';
NAME         POSITION DATATYPE_STRING      WAS VALUE_STRING                                                           ANYDATA.ACCESSTIMESTAMP(VALUE_ANYDATA)
---------- ---------- -------------------- --- ---------------------------------------------------------------------- ---------------------------------------------------------------------------
**:1                  1 VARCHAR2(128)        NO
:2                  2 TIMESTAMP            YES                                                                        20-DEC-22 06.11.14.000000000 AM
:1                  1 VARCHAR2(128)        NO
:2                  2 TIMESTAMP            YES                                                                        23-OCT-22 08.52.35.639000000 AM**

mos相关说明

V$SQL_BIND_CAPTURE Does Not Show The Value For Binds Of Type TIMESTAMP (文档 ID 444551.1)

SOLUTION

The bind value for the TIMESTAMP datatype could be obtained using a workaround of selecting the bind data via “ANYDATA.AccessTimestamp(value_anydata)”.