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 :-D

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 8-)

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 thoughts on “positive infinity”

  1. probably easy to use utl_raw ;-)

    SQL> select utl_raw.cast_to_number('FF65') from dual;
    
    UTL_RAW.CAST_TO_NUMBER('FF65')
    ------------------------------
                                 ~
    
    SQL> select utl_raw.cast_to_number('00') from dual;
    
    UTL_RAW.CAST_TO_NUMBER('00')
    ----------------------------
                              -~
    

  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:
    SELECT UTL_RAW.CAST_TO_NUMBER( ‘FF65′ ) – UTL_RAW.CAST_TO_NUMBER( ‘FF65′ ) FROM dual;
    (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
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm
    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;
    
    T
    -
    ~
    

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34570
    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.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>