Re: Storing a "NaN" (not a number) value

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 24 Oct 2013 20:54:44 +0400
Message-ID: <CAOVevU5Ok3s=fDWrJgp8tyTFdkLsJL0NO2uX86XeJVox6EExPQ_at_mail.gmail.com>

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

24.10.2013 18:52 ÐÏÌØÚÏ×ÁÔÅÌØ "Hemant K Chitale" <hemantkchitale_at_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
>
>
>

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

Received on Thu Oct 24 2013 - 18:54:44 CEST