Oracle FLOAT

Summary: this tutorial will introduce you to the Oracle FLOAT data type and show you how to apply it to define float columns for a table.

Introduction to Oracle FLOAT data type

The Oracle FLOAT data type is the subtype of the NUMBER data type. Its main purpose is to facilitate compatibility with ANSI SQL FLOAT data types.

The following shows the syntax of the FLOAT data type:

FLOAT(p)Code language: SQL (Structured Query Language) (sql)

You can only specify the precision for the FLOAT data type. You cannot specify the scale because the Oracle Database interprets the scale from the data. The maximum precision of FLOAT is 126.

In FLOAT, the precision is in binary bits, while in NUMBER the precision is in decimal digits. You use the following formula to convert between binary and decimal precision:

P(d) = 0.30103 * P(b)Code language: SQL (Structured Query Language) (sql)

According to this formula, the maximum of 126 digits of binary precision roughly equals 38 digits of decimal precision.

To make it compatible with the SQL ANSI FLOAT, Oracle provides a number of aliases as shown in the following table:

For example, instead of using the FLOAT(63) data type, you can use the REAL alias.

Oracle FLOAT example

First, create a new table named float_demo for the demonstration:

CREATE TABLE float_demo (
    f1 FLOAT(1),
    f2 FLOAT(4),
    f3 FLOAT(7)
);Code language: SQL (Structured Query Language) (sql)

Second, insert a new row into the float_demo table:

INSERT
    INTO
        float_demo(
            f1,
            f2,
            f3
        )
    VALUES(
        1 / 3,
        1 / 3,
        1 / 3
    );Code language: SQL (Structured Query Language) (sql)

Third, query data from the float_demo table:

SELECT
    *
FROM
    float_demo;Code language: SQL (Structured Query Language) (sql)

In this example, the data type of the column f1, f2 and f3 is FLOAT(1), FLOAT(4), and FLOAT(7). So the corresponding precision in decimal digits of the column f1, f2, and f3 is 1 (1 * 0.30103), 2 (4 * 0.30103), and 3 (7 * 0.30103).

In this tutorial, you have learned about the Oracle FLOAT data type and how to apply it to define float columns for a table.