Oracle FLOAT

Summary: In this tutorial, you’ll learn how to use Oraccle FLOAT data type to define float columns for tables.

Introduction to Oracle FLOAT data type #

In Oraccle, the FLOAT data type is the subtype of the NUMBER data type. Its main purpose is to facilitate compatibility with ANSI SQL FLOAT data types.

Here’s the syntax of the FLOAT data type:

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

In this syntax, Oracle allows you to specify the precision for the FLOAT data type. It does not let you specify the scale because the Oracle Database interprets the scale from the value. The maximum value of the precision (p) is 126 bits.

Oracle uses binary bits to measure the pricision in floating-point types. To convert binary precision to decimal precision, you use the following formula:

P(decimal) = 0.30103 * P(binary)Code language: SQL (Structured Query Language) (sql)

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).

Summary #

• The FLOAT type is a subtype of the NUMBER type.
• Oraccle uses bits to represent the precision of the floating number type.