Trailing Zeroes (000) Do Not Display in Decimal Place When Using Number Precision (Doc ID 764162.1)

Last updated on MARCH 08, 2017

Applies to:

SQL*Plus - Version: 10.2.0.3 to 11.1.0.6.0
This problem can occur on any platform.

Symptoms


When using Number precision [ie. number (10,5)], the trailing zeros in the decimal places (1.48000) are getting dropped (1.48).

1.  Connect to database -- 
SQL> connect test/test@R11DB 
Connected. 
2.  Run the below --  
SQL> show numwidth 
numwidth 10 
SQL> show numformat 
numformat "" 

SQL> create table test  
  2  (no number(10), value number(10,5)); 

Table created. 

SQL> desc test  
 Name                                      Null?    Type 
 ----------------------------------------- -------- 
---------------------------- 
 NO                                                 NUMBER(10) 
 VALUE                                              NUMBER(10,5) 

SQL> insert into test values 
  2  (10,1.480); 

1 row created. 

SQL> insert into test values 
  2  (1, 1.4805); 

1 row created. 

SQL> insert into test values 
  2  (2,1.4805678); 

1 row created. 

SQL> insert into test values 
  2  (3,1.48000); 

1 row created. 

SQL> select * from test; 

        NO      VALUE 
---------- ---------- 
        10       1.48 
         1     1.4805 
         2    1.48057 
         3       1.48 

CONCLUSION:  Note how the trailing zeros (0 & 000) in the decimal place are 
dropped for no 10 & 3, respectively. 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms