代码之家 › 专栏 › 技术社区 › Michael Pakhantsov

神谕。日期数据类型的强制转换收集

oracle

Michael Pakhantsov · 技术社区 · 6 年前

考虑类型:

CREATE OR REPLACE   TYPE date_array AS  TABLE OF DATE; 
CREATE OR REPLACE   TYPE number_array AS  TABLE OF NUMBER;
CREATE OR REPLACE   TYPE char_array AS  TABLE OF VARCHAR2(80);

查询:

WITH q AS
 (SELECT LEVEL ID,
         TRUNC(SYSDATE) + LEVEL MyDate,
         to_char(LEVEL) STRING
  FROM   dual
  CONNECT BY LEVEL < 5)
SELECT CAST(COLLECT(ID) AS number_array)
FROM   q;

WITH q AS
 (SELECT LEVEL ID,
         TRUNC(SYSDATE) + LEVEL MyDate,
         to_char(LEVEL) STRING
  FROM   dual
  CONNECT BY LEVEL < 5)
SELECT CAST(COLLECT(STRING) AS char_array)
FROM   q;

返回字符串集合

WITH q AS
 (SELECT LEVEL ID,
         TRUNC(SYSDATE) + LEVEL MyDate,
         to_char(LEVEL) STRING
  FROM   dual
  CONNECT BY LEVEL < 5)
SELECT CAST(COLLECT(MyDate) AS date_array)
FROM   q

返回错误 invalid datatype .

有人能解释为什么日期数据类型的行为不同吗?

1 回复 | 直到 6 年前

Carlo Sirna 6 年前

这是我的发现。。。似乎您正面临一个bug,这是由于计算日期似乎与“数据库”日期具有不同的内部表示形式。我确实找到了解决办法,所以继续读下去。

在我的oracle dev安装(oracle 11g Enterprise Edition 11.2.0.4.0-64位生产版)中,我遇到了与您相同的问题。

但是。。。如果我创建一个

create table test_data as 
SELECT LEVEL ID,
       TRUNC(SYSDATE) + LEVEL MyDate,
       to_char(LEVEL) STRING
FROM   dual
CONNECT BY LEVEL < 5

然后在此物理表上运行“cast collect”操作符,它将按预期工作:

-- this one works perfectly
SELECT CAST(COLLECT(MyDate) AS date_array) from test_data

-- here I just added 1 .. and it doesn't work
SELECT CAST(COLLECT(MyDate + 1) AS date_array) 
from test_data

-- here I am extracting sysdate, instead of a physical column... and it doesn't work
SELECT CAST(COLLECT(sysdate) AS date_array) 
from test_data

好像甲骨文不这么认为日期是一回事日期

所以,我试图“说服”甲骨文,我提供的数据实际上是一个正常的日期值,使用显式转换。。。还有尤里卡!这样做正确:

  WITH q AS
  (SELECT LEVEL ID,                                     
          -- this apparently unnecessary cast does the trick
          CAST( TRUNC(SYSDATE) + LEVEL AS DATE) MyDate, 
          to_char(LEVEL) STRING
   FROM   dual
   CONNECT BY LEVEL < 5)
 SELECT CAST(COLLECT(MyDate) AS date_array)
 FROM   q

对。。。但是为什么??

这两个值似乎并不完全相同,即使我们看到的值实际上是相同的:

 select sysdate, cast (sysdate as date) from dual

所以我深入研究了这两个值的内部表示,将“dump”函数应用于这两个值:

 select dump(sysdate), dump(cast (sysdate as date)) from dual

我得到的结果如下:

DUMP(SYSDATE                    )  -> Typ=13 Len=8: 226,7,11,9,19,20,47,0   
DUMP(CAST(SYSDATEASDATE) as DUAL)  -> Typ=12 Len=7: 120,118,11,9,20,21,48

不管怎样,我发现了更多。。似乎有人注意到了这一点: https://community.oracle.com/thread/4122627

http://psoug.org/reference/datatypes.html

里面有一个很长的关于日期的注释。。。摘录如下:

“怎么了?以上信息是否不正确或转储() 返回的是外部日期数据类型13而不是12。这种情况发生了因为我们依赖于TO\u DATE函数!外部数据类型13是 c编译器表示结构。请注意,“Len=”值是8 而不是7。类型13不是已发布的3GL接口的一部分操作。请注意,在转储

不管怎样,我再说一遍:我认为这是一个bug,但至少我找到了一个解决方法:使用显式cast。