Storing a "NaN" (not a number) value

From: Hemant K Chitale <hemantkchitale_at_gmail.com>
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