How to extend decimal places of a number to a minimum in Oracle PLSQL?

Question

I cannot figure out how to choose the following:

123 -> 123.00000 123.12 -> 123.12000 123.123456 -> 123.123456 

I would like to increase the number of decimal places to, for example, five decimal places (minimum) If there are no decimal places at all, there should be 5 zeros. This is normal if there are more than 5 decimal places.

 SELECT ROUND(123,5) FROM DUAL; 

: 123 instead of 123.00000

The number has precision by default.

Is this possible, or should I convert it to varchar with oracle formats ?

I am using Oracle 10g with PLSQL.

Answer 1

You can use the following:

 SQL> SELECT X, to_char(X, 'fm99999999.00000999') 2 FROM (SELECT 123 X FROM dual UNION ALL 3 SELECT 123.12 FROM dual UNION ALL 4 SELECT 123.123456 FROM dual); X TO_CHAR(X,'FM99999999.00000999 ---------- ------------------------------ 123 123.00000 123.12 123.12000 123.123456 123.123456 

Answer 2

You need to convert it to Varchar as follows:

 SELECT to_char(123, '9999.99999') from dual; 

Answer 3

if you want to format the data, you must use the to_char function. in numerical values, trailing zeros are truncated (they are irrelevant).