positive infinity

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;


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

SQL> select -f from t;


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


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


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

5 Replies to “positive infinity”

  1. probably easy to use utl_raw 😉

    SQL> select utl_raw.cast_to_number('FF65') from dual;
    SQL> select utl_raw.cast_to_number('00') from dual;

  2. Really nice to be able to create ~ and -~. For years I was keeping a copy of the numbers, copied from ixora (http://www.ixora.com.au/notes/infinity.htm)
    Now we can just use UTL_RAW.CAST_TO_NUMBER( ‘FF65′ ) and UTL_RAW.CAST_TO_NUMBER( ’00’ ) !
    But I am quite unhappy of the following:
    (Funny enough, the same with ’00’ instead of ‘FF65’ gives the ORA-01426)

  3. Indeed, ~ – ~ should not be 0… and I am not going to open a bug for this !

    In 10.2 positive infinity was still documented
    Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

    Since in 11g even the import itself is no longer supported, the comment as been removed.

    However we still have

    select to_char(9.9999999999999999999999999999999999e125) from dual;

    If a positive NUMBER value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value.

Leave a Reply

Your email address will not be published.