How does Oracle convert decimal values to float?

This answer presents a hypothesis fitting available information (excluding inconsistencies in the documentation linked below), that FLOAT(x) is a floating-point type with ⌈x log₁₀ 2⌉ decimal digits in its significand.

The examples in the question appear to come from this page pointed out by user gsalem, or a version of it.

First, we note the page discusses a NUMBER type, parameterized by a precision p and a scale s. The precision p is a number of decimal digits. Specifically, the page says it is “the maximum number of significant decimal digits.” (The page suggests the actual storage may use base 100, where it mentions “precision of up to 20 base-100 digits.” Software might provide decimal behavior while using base 100. For example, with three decimal digits of precision, the first base-100 digit could be any digit from 0 to 99, while the second digit would be limited to 0, 10, 20, 30, 40, 50, 60, 70, 80, or 90.)

The page then shows a number of examples that are consistent with the above. I note it appears to be a fixed-point format; the scale s is not a bound on a floating exponent but is a fixed exponent for the format. For example, converting 123.89 to NUMBER(6, -2) yields 100, which is consistent with a format containing six significant digits with the lowest in the 10² position (the - of -2 meaning to the left of the decimal point). That is, this format would have decimal numerals in the form abcdef00, so the closest it can get to 123.89 is 00000100, or 100.

Next, the page discusses the FLOAT type, which it says is a “subtype” of NUMBER. It also says “It can be specified with or without precision, which has the same definition it has for NUMBER” (emphasis added). But this is clearly false: If FLOAT(5) meant 5 decimal digits, as it did for NUMBER, then 1.23, 7.89, 12.79, and 123.45 could all be represented in FLOAT(5), but the examples show they are not.

Cryptically, the page says “To convert from binary to decimal precision, multiply n by 0.30103.” It has neither introduced n nor explained what binary precision we are converting from, nor where the result of this conversion is used. We will come back to this.

The page gives us these examples:

As explanation, the page states “the FLOAT value must be truncated so that its significant digits do not require more than 5 binary digits. Thus 123.45 is rounded to 120, which has only two significant decimal digits, requiring only 4 binary digits.” However, this explanation is inadequate. Converting 123.45 to no more than 5 binary digits would allow the closer value 124, which is 1111100₂ in binary, which has exactly 5 significant binary digits.

Further, converting 7.89 to no more than 5 binary digits cannot yield 7.9. The closest representable values are 1000.0₂ (decimal 8) and 111.11₂ (decimal 7.75).

Therefore, we conclude this discussion of binary digits as a limit explaining the examples is rubbish.

I propose this hypothesis: FLOAT(x) provides a type with ⌈x log₁₀ 2⌉ decimal digits (in code, ceil(x*log(2)/log(10))). Thus FLOAT(5) provides ⌈5 log₁₀ 2⌉ = ⌈1.505…⌉ = 2 decimal digits. Observe this fits the examples given in the page: 1.23, 7.89, 12.79, and 123.45 each map to the closest decimal numeral with two significant digits, 1.2, 7.9, 13, and 120.

Also note this is not a fixed-point format. Each of the numbers has two significant decimal digits, but they appear at varying positions: 10⁰ to 10⁻¹ in the first two, 10¹ to 10⁰ in the third, and 10² to 10¹ in the fourth. This is a floating-point format, which is consistent with the page’s statement that “Scale cannot be specified, but is interpreted from the data” and inconsistent with the statement that “The FLOAT data type is a subtype of NUMBER.” It is not a subtype but is a similar format (decimal with a fixed number of digits) with a floating exponent instead of a fixed exponent.

The FLOAT data-type is not a binary data-type; the values are stored in a proprietary format as pairs of decimal digits (base-100) so that an exact numeric representation of the number can be stored, up to a given precision.

Comparing the NUMBER data-types:

• FLOAT[b] stores up to d = ceil(log(10,2)*b) decimal digits where values have up to d significant figures and a variable scale.
• NUMBER[d] stores up to d digits where values have a precision of d decimal digits and a scale of 0.
• NUMBER[*,d] stores an unknown number of digits with a scale of d.

Looking at how numbers are represented in FLOAT columns if varying lengths:

CREATE TABLE table_name (
  f1 FLOAT(1),
  f2 FLOAT(2),
  f3 FLOAT(3),
  f4 FLOAT(4),
  f5 FLOAT(5),
  f6 FLOAT(6),
  f7 FLOAT(7),
  f8 FLOAT(8),
  n5 NUMBER(5),
  n_5 NUMBER(*,5)
);

INSERT INTO table_name
  SELECT 1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1),
         1.23456789 * POWER(10, LEVEL-1)
 FROM DUAL
 CONNECT BY LEVEL <= 5;

Then querying the values and the DUMP showing the underlying binary values settled in the database:

SELECT f1, DUMP(f1),
       f2, DUMP(f2),
       f3, DUMP(f3),
       f4, DUMP(f4),
       f5, DUMP(f5),
       f6, DUMP(f6),
       f7, DUMP(f7),
       f8, DUMP(f8),
       n5, DUMP(n5),
       n_5, DUMP(n_5)
FROM   table_name;

Outputs:

Which show that:

• FLOAT(1), FLOAT(2) and FLOAT(3) all store 1 significant decimal digit with identical values;
• FLOAT(4), FLOAT(5) and FLOAT(6) all store 2 significant decimal digits with identical values;
• FLOAT(7) and FLOAT(8) all store 3 significant decimal digits with identical values.

fiddle

Note: If you want to store values as binary floating point numbers then use the BINARY_FLOAT or BINARY_DOUBLE data-types.