greatest(BINARY_DOUBLE_INFINITY, BINARY_DOUBLE_NAN) GRE
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…
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;