When / Why Oracle Adds NaN to a Row in a Database Table

Question

I know NaN means Not a Number. But it's hard for me to understand when and why Oracle adds this to the string.

This is when it meets a value less than 0, as a negative number or when its value is garbage.

Answer 1

From the doc :

Oracle Database's numeric data types store positive and negative fixed and floating point numbers, zero, infinity, and the values ​​that result from an undefined operation - not a number or NAN .

As far as I know, you can get NaN only in the binary_float or binary_double column ; these data types have their own literals for NaN, too , and there is a condition is nan , and nanvl() function to manipulate them.

An example of a way to get such a value is dividing the float / double value by zero:

 select 0f/0 from dual; 0F/0 ---- NaN 

... so if you see NaNs, your application logic or underlying data may be broken. (Note: you cannot get this with the "normal" type of number, you will get ORA-01476: divisor is equal to zero if the numerator is not floating or double).

You will not get NaN for a zero or negative number. It is also possible that you have a string column and the application puts the word "NaN", but storing numbers as strings is a bad idea at many levels, so hopefully this is not so.

Answer 2

Nope <= 0 is still a number, so not really. NaN (or infinity) are special values ​​that a database uses to keep it reasonable when dealing with non-computable numbers (+ -∞ or just something that is not a number). Here is the 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; / 

Replace NAN with INFINITY or even cancel it and see the results.