How to display a number with fixed number of digits before the decimal?

I am looking for result 0123.56 and the following query should have worked.

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

SQL>

But it doesn't. What am I doing wrong here?

It looks like you are trying to convert a number to a number - try to_char instead:

select to_char(123.56,'0999.99') from dual;

TO_CHAR(123.56,'0999.99') 
------------------------- 
 0123.56

Note that in contrast, select to_number('0123.56','0999.99') from dual; will succeed because the format specification matches, but will not return the text 0123.56 as you want - the '0999.99' in this case is an input format, not an output format - the number 123.56 will be returned.

StackExchange Database Administrators Q#4999, answer score: 8