Storing a "NaN" (not a number) value
Date: Thu, 24 Oct 2013 22:52:13 +0800
Message-ID: <CAMNBsZveX10tR_3UA_y_sLTEPncQae8HFpB+pyPJJE9nKRDFwg_at_mail.gmail.com>
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>
--
Hemant K Chitale
http://hemantoracledba.blogspot.com
http://hemantscribbles.blogspot.com
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 24 2013 - 16:52:13 CEST