Laurent Schneider

I have read a long long time ago the following note on positive infinity http://www.ixora.com.au/notes/infinity.htm

Today I finally succeeded in inserting positive infinity in a number field

create table t as select 
  STATS_F_TEST(cust_gender, 1, 'STATISTIC','F') f
from (
  select 'M' cust_gender from dual union all 
  select 'M' from dual union all 
  select 'F' from dual union all 
  select 'F' from dual)
;

I am so happy

Let’s try a few queries

SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------
 F                          NUMBER

SQL> select f from t;

         F
----------
         ~

SQL> select f/2 from t;
select f/2 from t
        *
ERROR at line 1:
ORA-01426: numeric overflow

SQL> select -f from t;

        -F
----------
        -~

SQL> select cast(f as binary_double) from t;

CAST(FASBINARY_DOUBLE)
----------------------
                   Inf

SQL> select * from t
  2  where cast(f as binary_double) = binary_double_infinity;

         F
----------
         ~

Now expect a lot of bugs with your oracle clients

Toad 9 for example returns

SQL>  select f from t
select f from t
              *
Error at line 1
OCI-22065: number to text translation for the given 
format causes overflow

This entry was posted on Thursday, August 30th, 2007 at 13:57 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl