Oracle NUMBER Comparisons

Question

Generally in programming, the floating point data types should not be compared for equality since the values stored are very often an approximation.

Can two non-integer Oracle NUMBER values be compared reliably for equality since they are stored differently (base-10)?

Answer 1

Yes, Oracle NUMBER types are precise. They're more like integers with a scale than float/double types. So a NUMBER(10,3) has 10 digits, 3 after the decimal point, which is really a 10 digit integer with a scale of 3. In fact, that's precise how Java BigDecimals work (being a BigInteger plus a scale internally).

Answer 2

Oracle NUMBER types are stored as sets of centesimal digits (that is base 100, not base 10), one digit per byte.

The first byte represents the exponent, other bytes represent mantissa.

That means that for extremely large numbers, even the integer numbers can be rounded:

SELECT  10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 -
        10000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001
FROM    dual


---
0