Oracle PL/SQL - Float and Double type

Introduction

The SQL data types BINARY_FLOAT and BINARY_DOUBLE represent single-precision and double-precision IEEE 754-format floating-point numbers, respectively.

BINARY_FLOAT and BINARY_DOUBLE computations do not raise exceptions.

You must check the values that they produce for conditions such as overflow and underflow by comparing them to predefined constants.

The following table lists and describes the predefined PL/SQL constants for BINARY_FLOAT and BINARY_DOUBLE.

Constant Description
BINARY_FLOAT_NAN1 BINARY_FLOAT value for which the condition IS NAN (not a number) is true
BINARY_FLOAT_INFINITY1Single-precision positive infinity
BINARY_FLOAT_MAX_NORMAL Maximum normal BINARY_FLOAT value
BINARY_FLOAT_MIN_NORMAL Minimum normal BINARY_FLOAT value
BINARY_FLOAT_MAX_SUBNORMALMaximum subnormal BINARY_FLOAT value
BINARY_FLOAT_MIN_SUBNORMALMinimum subnormal BINARY_FLOAT value
BINARY_DOUBLE_NAN1BINARY_DOUBLE value for which the condition IS NAN (not a number) is true
BINARY_DOUBLE_INFINITY1 Double-precision positive infinity
BINARY_DOUBLE_MAX_NORMAL Maximum normal BINARY_DOUBLE value
BINARY_DOUBLE_MIN_NORMAL Minimum normal BINARY_DOUBLE value
BINARY_DOUBLE_MAX_SUBNORMAL Maximum subnormal BINARY_DOUBLE value
BINARY_DOUBLE_MIN_SUBNORMAL Minimum subnormal BINARY_DOUBLE value

Additional PL/SQL Subtypes of BINARY_FLOAT and BINARY_DOUBLE

PL/SQL predefines these subtypes:

  • SIMPLE_FLOAT, a subtype of SQL data type BINARY_FLOAT
  • SIMPLE_DOUBLE, a subtype of SQL data type BINARY_DOUBLE

Each subtype has the same range as its base type and has a NOT NULL constraint.

If you know that a variable will never have the value NULL, declare it as SIMPLE_FLOAT or SIMPLE_DOUBLE, rather than BINARY_FLOAT or BINARY_DOUBLE.

The performance improvement is greater with PLSQL_CODE_TYPE='NATIVE' than with PLSQL_CODE_TYPE='INTERPRETED'.