Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
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;



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?

share|improve this question
up vote 6 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.