Hi Tom (or Chris or Connor),
While i was trying something out i came upon a strange feature when using to_char.
I have 2 columns in a with clause, a value and a format.
Then I use these 2 in a to_char function.
The strange thing is that the format seems to get disrupted upon using it.
In this script is a test case:
https://livesql.oracle.com/apex/livesql/file/content_DPL2BW6XE7408R622JOT4AJF6.html I really can't think of any explanation what is happening, can you?
Thanks in advance
This looks like a query transformation bug. For example, if we force the resolution of the WITH clause, then we're ok
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.
If we look at the 10053 trace, you can see what the query was transformed to before execution,
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"
and hence the error
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
I'd advise you contact Support with the test case, and they'll log a bug for you.