I know that NaN stands for Not a Number. But, I have trouble understanding when and why Oracle adds this to a row.
Is it when it encounters a value less than 0 like a negative number or when its a garbage value.
1
-
0 and negative value are also valid, so they are not NaN. So Garbage value could be a reason, although you have to make an effort to insert a garbage value.GolezTrol– GolezTrol2015-02-11 18:10:10 +00:00Commented Feb 11, 2015 at 18:10
Add a comment
|
2 Answers
The Oracle Database numeric data types store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation—"not a number" or
NAN.
As far as I'm aware you can only get NaN in a binary_float or binary_double column; those data types have their own literals for NaN too, and there's an is nan condition for them too, and the nanvl() function to manipulate them.
An example of a way to get such a value is to divide a zero float/double value by zero:
select 0f/0 from dual;
0F/0
----
NaN
... so if you're seeing NaNs your application logic or underlying data might be broken. (Note you can't get this with a 'normal' number type; you get ORA-01476: divisor is equal to zero unless the numerator is float or double).
You won't get NaN for zero or negative numbers though. It's also possible you have a string column and an application is putting the word 'NaN' in, but storing numbers as strings is a bad idea on many levels, so hopefully that is not the case.
4 Comments
Ben
I'd be willing to be the last one... the only time I've ever run into NaN in Oracle that wasn't created by a runaway application somewhere was when I was trying to be too clever and failing miserably :-).
Shenoy Tinny
Thanks Alex. You nailed it. I was getting a NaN because of a division by 0
kirhgoff
But what if NaN is a normal value to show that the number cannot be calculated with given input to distinguish from missing value which is shown by nulls?
Alex Poole
@kirhgoff - true NaN is a normal 'value' but only applies for these two data types. Displaying NaN as a string (or storing, which would be bad) based on application logic, maybe when an ORA-01476 is encountered, is completely different. So not really sure what you mean. If you are doing something else or this doesn't address your issue you should ask a new question.
Nope <=0 is still a number so not quite. NaN (or infinity) are special values that the DB uses to keep it's sanity when dealing with non-computable numbers (+-∞, or simply something that is not a number). Here's some code:
DECLARE
l_bd_test binary_double;
l_int_test INTEGER;
BEGIN
l_bd_test := 'NAN';
l_int_test := 0;
IF l_bd_test IS NAN THEN
DBMS_OUTPUT.PUT_LINE(l_bd_test || ' IS NAN');
ELSE
DBMS_OUTPUT.PUT_LINE(l_bd_test || ' IS A #');
END IF;
IF l_int_test IS NAN THEN
DBMS_OUTPUT.PUT_LINE(l_int_test || ' IS NAN');
ELSE
DBMS_OUTPUT.PUT_LINE(l_int_test || ' IS A #');
END IF;
END;
/
Substitute NAN for INFINITY or even negate it and see the results.
