Does Oracle store trailing zeroes for Number data type?

Question

When i am storing numeric values in a table and querying that table in SQL Developer, it is not showing trailing zeroes for Number data type.

create table decimal_test(decimal_field number(*,10));

insert into decimal_test(decimal_field) values(10);
insert into decimal_test(decimal_field) values(10.11);
insert into decimal_test(decimal_field) values(10.100);
insert into decimal_test(decimal_field) values(10.00);

select * from decimal_test;

and the results are

10
10.11
10.1
10

These values are processed from java code. In this case i am using BigDecimal to store the value.

Before saving to DB, if i have BigDecimal(10.00), after saving, the value returned from DB is BigDecimal(10). Equals method in BigDecimal fails because the scale is changed.

And i the decimal precision is not constant. User can set BigDecimal(10.00) or BigDecimal(10.000) etc. Because of this, value needs to stored in DB as it is.

Is there any way to store the trailing zeros in oracle?

Answer 1

The existence of the trailing zeros is a display issue, not a storage issue. The trailing zeros are not significant, and anyway the internal format of the numbers is immaterial as long as the values are correct. There is no value difference between 10 and 10.00000.

If you need trailing zeros you can always use formatting when converting the values for display. For example:

System.out.printf("%10.4d\n", decimalValue);

If the problem is differences in scale, you can set the scale to the appropriate value before comparing.

Answer 2

If you want to preserve the scale of the numeric input (which can be significant - a measure of 10.1 is different from a measure 10.100) store the scale in an extra column.

create table decimal_test
(decimal_field number(*,10),
 decimal_scale number(*,0)
);

Calculate the scale of the input and store it in the new column decimal_scale.

Use this value in the output formating.

I'd not recommend to store the number as a string - you preserve the scale but loose the number validation and potentially confuse the CBO with wrong estimation caused by the type conversion...