Go ahead, use Float and Double datatypes in Oracle for Money

The nature of decimal types aren’t the same across popular RDBMS

For databases such as MySql and Postgres, using Float and Double columns types to store things like money is erroneous and can lead to strange results. This is due to Float and Decimal (and Real) being known as ‘approximated datatypes’, in other words they’re represented in formats like the IEEE 754 and stored in a field as binary. So that the value you store into the field is approximated when you retrieve it. The format of the stored binary is segmented for a sign bit on the highest order bit, an exponent (of a base 2) over the remaining 7 bit of the first byte, and the remaining bytes are allocated to the mantissa (3 bytes for single precision 7 for double).

In this example above, we store a value of 154.23 into a float field in a mysql database table. Then we analyze how the decoding works at a very high-level. Notice the IEEE 754 approximation; it is not the exact value we stored. Rather it’s very close to it, but if our requirements call of exact quantities, this will introduce accounting errors.

These datatypes are useful in speeding up certain computation (base-2 representations are in binary and benefit from floating-point circuitry in hardware) but if you require a query result to be absolutely exact as it was stored into the table, then do not use those datatypes. These types may have a lower approximation precision than what you require (
You wouldn’t want to save $154.23 into a record and get $154.229 back… )
More info here: https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html & https://www.postgresql.org/docs/9.3/datatype-numeric.html#DATATYPE-FLOAT

However the same is not entirely true for Oracle databases. In Oracle there is a numerical type “Number” and it is configured by two parameters: precision and scale. Precision determines how ‘big’ of a number the field can store (including the digits after the decimal), while scale determines its decimal precision (how precise the value is to the nth digit after the decimal). This type is know as the ‘decimal precision’ branch of data types that oracle offers. It stores a base-10 representation, as opposed to the base-2 representations such as IEEE 754.

Majority of the numerical data types in Oracle are based on this Number type. Double, Double Precision, Float, Integer and Real are all aliases of the Number type with preconfigured precision and scale values. The datatype utilizes an exponent and mantissa (fraction) to store decimal values, representing upwards of 38 decimal digits over a maximum of 22 bytes. The format calls for a variable length mantissa, allowing the column width to be defined by the precision.

In this example we see that the value 154.23 is stored using 4 bytes: c2 02 37 18. And because it is stored in base-10, it is recovered precisely as it was stored. In a nut a shell, the format stores the value in the mantissa and stores the decimal location as the exponent. While base-2 formats like the IEEE 754 allow approximations from a maximum of eight bytes (double precision) and operate in base-2 (for machine level optimizations), the trade off is the precision being affected by rounding errors. Oracle’s Number format on the other hand leans towards decimal representations and exact value recovery.

Oracle still offers a base-2 style datatype meant for computational efficiencies. The types BINARY_FLOAT (single precision; 4 bytes) and BINARY_DOUBLE (double precision; 8 bytes) are equivalent to the Float and Double types from MySQl and Postgres databases. They will suffer from the same round error issues.

Naturally this is a high-level discussion here and Oracle has put in a lot of design consideration into making Number the de facto numerical storage representation, including optimizations necessary for computation and querying. But those are a topic for another day.