select BINARY_DOUBLE_INFINITY INF, BINARY_DOUBLE_NAN NAN, greatest(BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN) GRE from t; INF NAN GRE --- --- --- Inf Nan Nan
Nan means not a number. It could be square root of -1, log of -1, 0/0, acos(1000), Inf-Inf, etc…
select SQRT(-1d), LN(-1d), 0/0d, acos(1000d), BINARY_DOUBLE_INFINITY-BINARY_DOUBLE_INFINITY from t; SQR LN- 00D ACO BIN --- --- --- --- --- Nan Nan Nan Nan Nan
According to the doc, it is greater than any value, inclusive positive infinity.
To check if a value is nan, it could be compared to BINARY_DOUBLE_NAN.
where :z = BINARY_DOUBLE_NAN
There is a function NANVL(:z, :y) which evaluates to :y when :z is equal Nan. if :z is not equal to Nan and :y is not null, then it evaluates to :z. NANVL evaluates to NULL when :z or :y is null.
select NANVL(1,null) from dual; NANVL ------ [null]