When is an Oracle Database NUMBER Column Not a Number (NaN)?

When it holds an infinite number, since “infinity” is not a number. Oracle Database OCM Franck Pachot reminded me recently about the floating point literals available in Oracle Database, such as binary_float_infinity and binary_double_infinity. Not only can you use them to compare to numbers that can’t be represented as numeric literals, but you can also populate table columns with them:

SQL> create table double_demo as
  2     select binary_double_infinity dinf from dual;

Table DOUBLE_DEMO created.

SQL> select dinf+1 from double_demo;

    DINF+1
----------
       Inf

SQL> 

Apparently, Infinity+1 is still Infinity! Comparison operators don’t seem to work as expected, but not surprisingly given that infinity is not really a number:

SQL> select
  2     case
  3        when dinf+1 > dinf then 'Double is better!'
  4        when dinf-1 < dinf then 'Less than nothing?'
  5        when dinf+1 = dinf then 'They are all the same.'
  6        else 'I have NO idea.'
  7     end inception_time
  8  from double_demo;

INCEPTION_TIME
----------------------
They are all the same.

SQL> 

As I am always curious about what goes on behind the scenes, I used the DUMP function to see what is actually stored in that column:

SQL> select dump(dinf) from double_demo;

DUMP(DINF)
------------------------------------
Typ=101 Len=8: 255,240,0,0,0,0,0,0

SQL>

So the next time someone asks you what infinity actually means, you can say that in Oracle Database, a double precision infinity = 0xFFF0000000000000.