Does Oracle Store Trailing Zeroes for Number Data Type

.NET code adds a trailing 0 to the numbers with 1 or 3 fractional digits stored in Oracle DB

I created a support ticket to Oracle. They told me that it is a BUG

Round a number and keep trailing zeroes in oracle

Sounds like you need to_char...

SELECT to_char(TRUNC(7836.721), '9999990.000') numbr
FROM   dual;

NUMBR
------------
    7837.000

This does assume that you're after 3 decimal places for any number, rather than 7.12 going to 7.00.

ETA:

It seems like a very strange requirement, but here you go:

WITH sample_data AS (SELECT 123 numbr FROM dual UNION ALL
                     SELECT 123.12 numbr FROM dual UNION ALL
                     SELECT 123.1234 numbr FROM dual UNION ALL
                     SELECT 123.12345 numbr FROM dual UNION ALL
                     SELECT 0.12345 numbr FROM dual UNION ALL
                     SELECT 1.2345678901234567890123456789012345678901234567890123e50 numbr FROM dual UNION ALL
                     SELECT NULL numbr FROM dual UNION ALL
                     SELECT 1.23e-50 numbr FROM dual)
SELECT numbr,
       ROUND(numbr) || CASE WHEN numbr != TRUNC(numbr) THEN 
                                 RPAD('.', LENGTH(numbr -TRUNC(numbr)), '0') END new_numbr
FROM   sample_data;

     NUMBR NEW_NUMBR
---------- --------------------------------------------------------------------------------
       123 123
    123.12 123.00
  123.1234 123.0000
 123.12345 123.00000
   0.12345 0.00000
1.23456789 1.2345678901234567890123456789012346E+50

  1.23E-50 0.000000000000000000000000000000000000000

Related Topics