NUMBER stores data using decimal precision. BINARY floating-point numbers are stored
using binary precision (the digits 0 and 1).
BINARY_FLOAT is a 32-bit, single-precision floating-point number
datatype. BINARY_DOUBLE is a 64-bit, double-precision floating-point number
datatype. You can not specify a precision when you define a column with the BINARY_FLOAT or BINARY_DOUBLE datatype.
For example,
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float(20));
f3 binary_float(20))
*
ERROR at line 4:ORA-00907: missing right parenthesis
After removing the precision from the create table statement, it can create table successfully.
Create table t1
(f1 number,
f2 varchar2(20),
f3 binary_float);
table created.
There are some special values for the BINARY_FLOAT/BINARY_DOUBLE datatypes.
BINARY_FLOAT_NAN A value of type BINARY_FLOAT for Not a Number
BINARY_DOUBLE_NAN A value of type BINARY_DOUBLE for Not a Number
BINARY_FLOAT_INFINITY A value of type BINARY_FLOAT for positive infinity(+∞)
BINARY_DOUBLE_INFINITY A value of type BINARY_DOUBLE for positive infinity(+∞)
Let's insert some rows using these special literal values.
insert into t1 values (1, 'string one', 1234567.89103245678);
insert into t1 values (2, '234.5678', BINARY_FLOAT_INFINITY);
insert into t1 values (3, 'string two', null);
insert into t1 values (4, '654.213', BINARY_FLOAT_NAN);
insert into t1 values (5, 'string three', -BINARY_FLOAT_INFINITY);
commit;
SQL> select * from t1;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
3 string two
4 654.213 Nan
5 string three -Inf
Now, we have 5 rows.
Note: We use the -BINARY_FLOAT_INFINITY for the negative infinity(-∞).
You can test not a number using IS [NOT] NAN in the WHERE clause. But this is only working with the BINARY_FLOAT ot BINARY_DOUBLE datatype.
select * from t1 where f2 is nan;
select * from t1 where f2 is nan
*
ERROR at line 1:ORA-01722: invalid number
This is because f2 is not a BINARY_FLOAT or BINARY_DOUBLE datatype. It is a VARCHAR2 datatype.
select * from t1 where f3 is nan;
F1 F2 F3
---------- -------------------- ----------
4 654.213 Nan
select * from t1 where f3 is not nan;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
5 string three -Inf
Note: NULL is neither a NAN, nor a not NAN. BINARY_FLOAT_INFINITY is special number, so not NAN is return TRUE for the BINARY_FLOAT_INFINITY value.
You can use the IS [NOT] INFINITY to test infinity condition.
SQL> select * from t1 where f3 is infinite;
F1 F2 F3
---------- -------------------- ----------
2 234.5678 Inf
5 string three -Inf
SQL> select * from t1 where f3 is not infinite;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
4 654.213 Nan
Note: NULL is neither a INFINITE number, nor a not INFINITE number.
SQL> select * from t1 where f3 is null;
F1 F2 F3
---------- -------------------- ----------
3 string two
SQL> select * from t1 where f3 is not null;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
2 234.5678 Inf
4 654.213 Nan
5 string three -Inf
BINARY_FLOAT_INFINITY and BINARY_FLOAT_NAN are special values. They are not NULL.
Binary floating-point numbers are stored using binary precision, so it can not represent all values
using decimal precision exactly.
SQL> select * from t1 where f3=1234567.89103245678;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.89103245;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567.8;
no rows selected
SQL> select * from t1 where f3=1234567.9;
F1 F2 F3
---------- -------------------- ----------
1 string one 1.235E+006
SQL> select * from t1 where f3=1234567;
no rows selected
SQL> select * from t1 where f3=1234568;
no rows selected
Now, we add another column for BINARY_DOUBLE datatype, and update the value of BINARY_DOUBLE column with the same value of the BINARY_FLOAT datatype.
update t1 set f4=1234567.89103245678 where f1=1;
update t1 set f4=binary_double_infinity where f1=2;
update t1 set f4=null where f1=3;
update t1 set f4=binary_double_nan where f1=4;
update t1 set f4=-binary_double_infinity where f1=5;
SQL> commit;
Commit complete.
SQL> select * from t1;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
2 234.5678 Inf Inf
3 string two
4 654.213 Nan Nan
5 string three -Inf -Inf
Note: The BINARY_FLOAT_INFINITY is identical with the BINARY_DOUBLE_INFINITY. Also, the BINARY_FLOAT_NAN is identical with the BINARY_DOUBLE_NAN.
The interesting thing is that the value of BINARY_FLOAT and BINARY_DOUBLE for row 1 is not equal for row 1, even you insert it using the same literal value.
SQL> select * from t1 where f3=f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
2 234.5678 Inf Inf
4 654.213 Nan Nan
5 string three -Inf -Inf
SQL> select * from t1 where f3<>f4;
F1 F2 F3 F4
---------- -------------------- ---------- ----------
1 string one 1.235E+006 1.235E+006
This is because Oracle stores different values for different datatype.
SQL> select dump(f3) dumpf3, dump(f4) dumpf4 from t1 where f1=1;
DUMPF3 DUMPF4
------------------------------ --------------------------------------------------
Typ=100 Len=4: 201,150,180,63 Typ=101 Len=8: 193,50,214,135,228,26,179,254
Also, you can insert BINARY_FLOAT_INFINITY value or BINARY_FLOAT_NAN value in a BINARY_DOUBLE datatype column, and insert BINARY_DOUBLE_INFINITY and BINARY_DOUBLE_NAN in a BINARY_FLOAT datatype column.
insert into t1 values (6, null, BINARY_FLOAT_INFINITY, BINARY_FLOAT_INFINITY);
insert into t1 values (7, null, BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_INFINITY);
commit;
SQL> select f1, f3, f4, dump(f3) dumpf3, dump(f4) dumpf4 from t1;
F1 F3 F4 DUMPF3 DUMPF4
---------- ---------- ---------- ------------------------------ ------------------------------------
1 1.235E+006 1.235E+006 Typ=100 Len=4: 201,150,180,63 Typ=101 Len=8: 193,50,214,135,228,26,179,254
2 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
3 NULL NULL
4 Nan Nan Typ=100 Len=4: 255,192,0,0 Typ=101 Len=8: 255,248,0,0,0,0,0,0
5 -Inf -Inf Typ=100 Len=4: 0,127,255,255 Typ=101 Len=8: 0,15,255,255,255,255,255,255
6 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
7 Inf Inf Typ=100 Len=4: 255,128,0,0 Typ=101 Len=8: 255,240,0,0,0,0,0,0
