oracle format specifiers: to_number vs to_char

Question

SQL> select TO_NUMBER(123.56,'999.9') from dual;
select TO_NUMBER(123.56,'999.9') from dual
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> select TO_CHAR(123.56,'999.9') from dual;

TO_CHA
------
 123.6

SQL>

I am having a hard time in understanding the nuances of Oracle SQL. For example, have a look at the two queries above.

Why does first query fail but second one succeed?

Answer 1

According to the documentation, the first value for a to_number needs to be

a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type containing a number in the format specified by the optional format model

Therefore Oracle is implicitly converting 123.56 into a character value. The problem then comes that the format for the to_number does not match the value being given. This can be seen because you get the same error for the following two variations:

select to_number(to_char(123.56),'999.9') from dual;

select to_number('123.56','999.9') from dual;

But you don't for this:

select to_number(123.56,'999.99') from dual;

When converting characters to numbers the format specification must match exactly, but when converting numbers to characters the number can be selectively extracted using the format specifier.