Storing a "NaN" (not a number) value

[Hemant K Chitale]

I need some hints / tips about loading and storing a NaN into an Oracle
table.
The incoming value would likely be from a flat file, so I'd have to be
using SQLLoader. Currently, the target table column is a NUMBER datatype.

I can see that I can insert a NaN into BINARY_FLOAT or BINARY_DOUBLE. (see
below). Is there a way to load / handle into a NUMBER column ?


SQL> drop table hkc_test_nan;

Table dropped.

SQL> create table hkc_test_nan (id number, val_nu number, val_bn_dbl
binary_double);

Table created.

SQL> insert into hkc_Test_nan values (0,0,0);

1 row created.

SQL> insert into hkc_Test_nan values (1,1,'Nan');

1 row created.

SQL> insert into hkc_test_nan values (2,'Nan',2);

insert into hkc_test_nan values (2,'Nan',2)

*

ERROR at line 1:

ORA-01722: invalid number

SQL> select id, val_nu, nanvl(val_bn_dbl,100) from hkc_test_nan;

ID VAL_NU NANVL(VAL_BN_DBL,100)

---------- ---------- ---------------------

0 0 0

1 1 1.0E+002

2 rows selected.

SQL> select id, val_nu, val_bn_dbl from hkc_test_nan;

ID VAL_NU VAL_BN_DBL

---------- ---------- ----------

0 0 0

1 1 Nan

2 rows selected.

SQL> select * from hkc_test_nan where val_bn_dbl is nan;

ID VAL_NU VAL_BN_DBL

---------- ---------- ----------

1 1 Nan

1 row selected.

SQL> select * from hkc_test_nan where val_bn_dbl is not nan;

ID VAL_NU VAL_BN_DBL

---------- ---------- ----------

0 0 0

1 row selected.

SQL>

[Andy Klock]

Post by Hemant K Chitale
I can see that I can insert a NaN into BINARY_FLOAT or BINARY_DOUBLE. (see
below). Is there a way to load / handle into a NUMBER column ?

You nailed it Hemant, only BINARY_FLOAT and BINARY_DOUBLE support special
values like NaN and infinity (INF). So if you need to support NaN the
target table will either need to be changed or you'll have to do something
fancy with the NANVL function.

Andy


--
http://www.freelists.org/webpage/oracle-l

[Sayan Malakshinov]

NaN, inf, -inf are just special values of binary_float and binary_double.
You can see it with dump() function, so there are no such values in numbers.
SQL> col nan_dump format a30;
SQL> col dump_n_number format a30;

SQL> select

2 cast('nan' as binary_float) nan_float

3 , dump(cast('nan' as binary_float),16) nan_dump

4 , utl_raw.cast_to_number('FFC00000') n_number

5 , dump(utl_raw.cast_to_number('FFC00000'),16) dump_n_number

6 , cast('nan' as binary_float) nan_float

7 from dual

8 /


NAN_FLOAT NAN_DUMP N_NUMBER DUMP_N_NUMBER
NAN_FLOAT

---------- ------------------------------ ----------
------------------------------ ----------

Nan Typ0 Len=4: ff,c0,0,0 Typ=2 Len=4: ff,c0,0,0
Nan

Post by Hemant K Chitale
I need some hints / tips about loading and storing a NaN into an Oracle
table.
The incoming value would likely be from a flat file, so I'd have to be
using SQLLoader. Currently, the target table column is a NUMBER datatype.
I can see that I can insert a NaN into BINARY_FLOAT or BINARY_DOUBLE. (see
below). Is there a way to load / handle into a NUMBER column ?
SQL> drop table hkc_test_nan;
Table dropped.
SQL> create table hkc_test_nan (id number, val_nu number, val_bn_dbl
binary_double);
Table created.
SQL> insert into hkc_Test_nan values (0,0,0);
1 row created.
SQL> insert into hkc_Test_nan values (1,1,'Nan');
1 row created.
SQL> insert into hkc_test_nan values (2,'Nan',2);
insert into hkc_test_nan values (2,'Nan',2)
*
ORA-01722: invalid number
SQL> select id, val_nu, nanvl(val_bn_dbl,100) from hkc_test_nan;
ID VAL_NU NANVL(VAL_BN_DBL,100)
---------- ---------- ---------------------
0 0 0
1 1 1.0E+002
2 rows selected.
SQL> select id, val_nu, val_bn_dbl from hkc_test_nan;
ID VAL_NU VAL_BN_DBL
---------- ---------- ----------
0 0 0
1 1 Nan
2 rows selected.
SQL> select * from hkc_test_nan where val_bn_dbl is nan;
ID VAL_NU VAL_BN_DBL
---------- ---------- ----------
1 1 Nan
1 row selected.
SQL> select * from hkc_test_nan where val_bn_dbl is not nan;
ID VAL_NU VAL_BN_DBL
---------- ---------- ----------
0 0 0
1 row selected.
SQL>
--
Hemant K Chitale
http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l