Neil Chandler's DB Blog

A long time ago, Oracle had a couple of special numbers that you were allowed to store in a NUMBER datatype. They were Infinity and Negative Infinity!

If you selected these numbers (using SQL*Plus) , they showed up as “~” and “-~“, although it is worth noting that if you’re using the funky modern sqlcl you get the words “Infinity” and “-Infinity”. Much nicer (although not exactly backward compatible), Jeff.

So when was this? Well, it was valid in Oracle 5 and removed as a feature in Oracle 6, which was released in 1988 (coincidentally the year I started working, and 37 years ago at the time of writing. Look I so old to young eyes? When nine hundred years old you reach, look as good you will not. And yes, I saw that on the cinema first time around.)

However, in keeping with backwards compatibility, it still works and new features – remarkably – still work with it. You can still insert, update and select infinity (and negative infinity) using the utl_raw.convert_to_number procedure.

---

So why am I talking about this old stuff? Well sometimes, at a client you come across an unusual situation… for Oracle 19C.

A client had a column with some numbers in it. They were summing the column and a change in the predicates on the column meant rows containing infinity values were now being included. The client was unaware of the infinity values, and somewhat confused that they were getting an error, and why.

I suppose that Oracle has 2 choices when summing data including an Infinity. Infinity + anything is infinity but you’d have to code explicitly for that and it might not be the number you want to get back**. Also, storing Infinity has not exactly been supported for 37 years. So, you actually get the error ORA-01426: numeric overflow as Oracle can’t store anything higher than Infinity (which would probably lead to new branch of maths).

I mean, I know there are different types of infinities, and some are demonstrably larger infinities than others. There’s an infinite set of numbers in the Fibonacci sequence, there is a larger amount of infinite even numbers, and an event larger set of infinite whole numbers. And whilst that’s all self evident, it’s way beyond my mathematics pay grade to explain why. (There is proof they are all the same size: https://www.quantamagazine.org/mathematicians-measure-infinities-find-theyre-equal-20170912/ – thanks Ilmar Kerm)

Lets finish with an example of holding infinite numbers in Oracle, just in case you come across them or fancy a play around.

create a table and insert infinity and negative infinity.
Have a 2nd row of data just to allow the column to be summed.

create table infinity (c1 number,c2 number,c3 number);
Table created.
insert into infinity values (1,utl_raw.cast_to_number('FF65'),utl_raw.cast_to_number('00'));
1 row created.
insert into infinity values (2,1,1);
1 row created.
commit;
Commit complete.

selecting the data in sqlplus and sqlcl

--sqlplus
select c1,rawtohex(c1),c2,rawtohex(c2),c2,rawtohex(c3) from infinity;
    C1 RAWTOHEX(C1)     C2 RAWTOHEX(C2)     C3 RAWTOHEX(C3)
------ -------- ---------- ------------ ------ ------------
     1 C102              ~ FF65             -~ 00
     2 C103              1 C102              1 C102
--sqlcl
select c1,rawtohex(c1),c2,rawtohex(c2),c2,rawtohex(c3) from infinity;
C1  RAWTOHEX(C1)         C2  RAWTOHEX(C2)       C3  RAWTOHEX(C3)
 1  C102           Infinity  FF65        -Infinity  00
 2  C103                  1  C102                1  C102

show how it errors, but that functions still work correctly

-- infinity plus 1
select sum(c2) from infinity;
select sum(c2) from infinity
       *
ERROR at line 1:
ORA-01426: numeric overflow

But is it still regarded as a number?

-- lets see if we can catch it on a conversion error
-- (it's already a number and you're not converting it)
select to_number(sum(c2) DEFAULT 0 on CONVERSION ERROR) from infinity
                 *
ERROR at line 1:
ORA-01426: numeric overflow
-- and the 12.2C+ function "validate_conversion" proves it
select c2 from infinity where validate_conversion(c2 as number) = 1;
        C2
----------
         ~
         1

Alternate way to represent infinity, for the completist.

select 9.9999999999999999999999999999999999999e125 from dual;
999999999999999999999999999999999999990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
------------------------------------------------------------------------------------------------------------------------------
                                                                                                                             ~

and because I know you’re wondering – what if we add infinity and negative infinity together… is that Zero?
(no – **infinity isn’t really a number in mathematics so you can’t treat it as such.)

select c2+c3 from infinity
         *
ERROR at line 1:
ORA-01426: numeric overflow
-- or take away
select c2-c3 from infinity
         *
ERROR at line 1:
ORA-01426: numeric overflow

Hope you find that as interesting as I did.

Now, go listen to Infinity by Hawkwind. Your ears will love you for it.

And if you really want to know a bit more about mathematical infinities, check out Hibbert’s Paradox of the Grand Hotel (thank you Jonathan Lewis, but I’m still baffled by it)

~

Finally, if you actually want to use infinity in Oracle, you need to use a floating point binary_double type – some more info here from Ilmar.