Take the 2-minute tour ×

Is there any way to preserve number precision (0.100 vs 0.1) in Oracle? The precision needs to be stored for accountability reasons.

I want to avoid storing these numbers as a string because there is also a set of stored procedures which do some number crunching on these values.

share|improve this question
Why not store the number of significant figures as separate int? –  NoBugs Aug 9 '12 at 2:57
add comment

3 Answers

up vote 1 down vote accepted

No. The numbers 0.100, 0.1, and .1 are all identical from Oracle's perspective. They'll all have identical internal representations.

If you need to store the precision, you'll need a second column to store the precision. Or you'll need to store the number in a VARCHAR2 column and convert it to a number before doing the number crunching. Of course, you'll need to define the rules for handling precision in your number crunching logic. If you add 0.100 and 0.22, for example, is the result 0.32 or 0.320?

share|improve this answer
add comment

I would suggest storing both the numeric value for queries and mathematical operations, and the string version "as entered" for audit purposes.

share|improve this answer
add comment

I would suggest if the use case only requires the additional digits to be displayed then convert it to a string as the last step before sending the data to the report:

    TO_CHAR(column_name, 999.999)
share|improve this answer
This does not preserve precision. –  duck9 Aug 9 '12 at 3:57
How does this not preserve your precision? 0.1 is the same as 0.10 and for all internal processes you will utilize 0.1. Only when the data needs to be reported out do you apparently want the degree of the precision to be displayed. Due to that use case you should convert as the final step using TO_CHAR. –  John D Aug 9 '12 at 18:18
add comment

Your Answer


