Oracle 使用后格式中断

嗨，汤姆 (或克里斯或康纳)，

当我尝试一些东西时，我遇到了一个奇怪的功能，当使用to_char。
我有2列在一个与子句，一个值和一个格式。
然后我在to_char函数中使用这2。
奇怪的是，该格式在使用后似乎会受到干扰。

在这个脚本是一个测试用例:
https://livesql.oracle.com/apex/livesql/file/content_DPL2BW6XE7408R622JOT4AJF6.html

我真的想不出任何解释发生了什么，是吗？


提前感谢

这看起来像一个查询转换bug。例如，如果我们强制解析与子句，那么我们就可以了

SQL> with table_values as
  2  (select 99.56784 as x
  3        , null           as y
  4        , '999G999G999G999G990D000'     as format1
  5        , '990.00'                      as format2
  6   from dual)
  7  , resultset1 as
  8  (select rownum
  9  , x
 10  , y
 11  , to_char(x, format1) as char_x
 12  , to_char(y, format1) as char_y
 13  , format1
 14  , format2
 15  from table_values)
 16  select rs1.*, to_char(rs1.x, rs1.format1) from resultset1 rs1;
select rs1.*, to_char(rs1.x, rs1.format1) from resultset1 rs1
                             *
ERROR at line 16:
ORA-01481: invalid number format model


SQL>
SQL>
SQL>
SQL> with table_values as
  2  (select /*+ materialize */ 99.56784 as x
  3        , null           as y
  4        , '999G999G999G999G990D000'     as format1
  5        , '990.00'                      as format2
  6   from dual)
  7  , resultset1 as
  8  (select rownum
  9  , x
 10  , y
 11  , to_char(x, format1) as char_x
 12  , to_char(y, format1) as char_y
 13  , format1
 14  , format2
 15  from table_values)
 16  select rs1.*, to_char(rs1.x, rs1.format1) from resultset1 rs1;

    ROWNUM          X Y
---------- ---------- -
CHAR_X
-------------------------------------------------------------------------------
CHAR_Y
-------------------------------------------------------------------------------
FORMAT1                 FORMAT
----------------------- ------
TO_CHAR(RS1.X,RS1.FORMAT1)
-------------------------------------------------------------------------------
         1   99.56784
                  99.568

999G999G999G999G990D000 990.00
                  99.568


1 row selected.

如果我们查看10053跟踪，您可以看到查询在执行之前转换为什么，

SELECT "RS1"."ROWNUM" "ROWNUM",
  "RS1"."X" "X",
  "RS1"."Y" "Y",
  "RS1"."CHAR_X" "CHAR_X",
  "RS1"."CHAR_Y" "CHAR_Y",
  "RS1"."FORMAT1" "FORMAT1",
  "RS1"."FORMAT2" "FORMAT2",
  TO_CHAR("RS1"."X",RAWTOHEX("RS1"."FORMAT1")) "TO_CHAR(RS1.X,RS1.FORMAT1)"
FROM
  (SELECT ROWNUM "ROWNUM",
    99.56784 "X",
    NULL "Y",
    TO_CHAR(99.56784,'999G999G999G999G990D000') "CHAR_X",
    TO_CHAR(TO_NUMBER(NULL),'999G999G999G999G990D000') "CHAR_Y",
    '999G999G999G999G990D000' "FORMAT1",
    '990.00' "FORMAT2"
  FROM "SYS"."DUAL" "DUAL"
  ) "RS1"

因此错误

SQL> SELECT "RS1"."ROWNUM" "ROWNUM",
  2    "RS1"."X" "X",
  3    "RS1"."Y" "Y",
  4    "RS1"."CHAR_X" "CHAR_X",
  5    "RS1"."CHAR_Y" "CHAR_Y",
  6    "RS1"."FORMAT1" "FORMAT1",
  7    "RS1"."FORMAT2" "FORMAT2",
  8    TO_CHAR("RS1"."X",RAWTOHEX("RS1"."FORMAT1")) "TO_CHAR(RS1.X,RS1.FORMAT1)"
  9  FROM
 10    (SELECT ROWNUM "ROWNUM",
 11      99.56784 "X",
 12      NULL "Y",
 13      TO_CHAR(99.56784,'999G999G999G999G990D000') "CHAR_X",
 14      TO_CHAR(TO_NUMBER(NULL),'999G999G999G999G990D000') "CHAR_Y",
 15      '999G999G999G999G990D000' "FORMAT1",
 16      '990.00' "FORMAT2"
 17    FROM "SYS"."DUAL" "DUAL"
 18    ) "RS1"
 19  /
  TO_CHAR("RS1"."X",RAWTOHEX("RS1"."FORMAT1")) "TO_CHAR(RS1.X,RS1.FORMAT1)"
                    *
ERROR at line 8:
ORA-01481: invalid number format model

我建议您通过测试用例与支持人员联系，他们将为您记录一个错误。