Does Oracle store trailing zeroes for Number data type?
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?
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.
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...
10 and 10.00 are generally the same numbers. (It doesn't make a difference if you have to pay 10 $ or 10.00 $ or even 10.0000 $ :-)
If, however, you want to express the precision of the value as in measurements, so 10.00 means you know the number only up to two decimal places, but the real number can be 10.00001 or 10.009 or whatever, then you must additonally store this information. One way to do this would be using a string datatype, but then you'd have to stick to a certain format ('10.00' or say '10,00') and shouldn't use this for calculations in the database.
Another option would be store the number and an additional column for the precision of decimal places, e.g. value = 10 / decimal_places = 2. I would prefer this over storing a string.