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;

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

5 thoughts on “positive infinity”

  2. Laurent Schneider Post author

    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')
    ----------------------------
    -~

  3. Pingback: Laurent Schneider » to_number(to_char(:n))

  4. BrunoVroman

    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)

  5. Laurent Schneider Post author

    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.

Comments are closed.