oracle format specifiers: to_number vs to_char

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?

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.

StackExchange Database Administrators Q#4941, answer score: 7