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

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

  •  0
  • 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 年前
        1
  •  3
  •   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。