Oracle stores numbers with the NUMBER
datatype in an unusual format, with bytes in binary-coded decimal
(where the byte hex value 0x99 represents 99 decimal). ANSI
standard datatypes such as INTEGER, FLOAT, and DOUBLE are aliases
for the NUMBER datatype, with extra restrictions: INTEGER datatypes
don’t allow decimal, FLOAT, and DOUBLE to restrict the number of
significant digits to fit in integer values.

The IEEE 754 standard format is the most common
format for representing real numbers on most computer operating
systems and programming languages. The internal format of floating
point numbers is always stored in either 32-bit (FLOAT) or 64-bit
(DOUBLE) values.

When data are fetched from the database into
host programming languages, numbers must be converted from one
format to another. There’s a small chance of precision loss when
converting numbers. Also, the process of converting from one type
to another can affect application performance, with large sets of
values being converted.

In Oracle 10g, the Oracle Call Interface, a
column, or PL/SQL variable can be bound to BINARY_FLOAT or
BINARY_DOUBLE datatypes. There’s no need to convert between these
datatypes to use the data in host programming environments, such as
Java, when casting or converting values and precision is
preserved.

The trade-off is speed vs. accuracy. Oracle
internal numbers are able to store up to 38 decimal significant
digits exactly. IEEE 754 floating point numbers can store only
about seven significant digits in binary. IEEE numbers also have
“gradual underflow” problems because binary values can often be
repeating values. For example, 0.1 is an exact value in Oracle
number representation but is a repeating decimal when converted to
binary.

TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!