Oracle Internals Notes

Positive and negative infinity (obsolete)

The NUMBER datatype still supports special encodings for positive and negative infinity. They require two bytes and one byte of storage space respectively. Positive infinity is displayed as ~, and negative infinity is displayed as -~. This is illustrated in the following listing.

SQL> describe infinity
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VALUE                                              NUMBER
 DESCRIPTION                                        CHAR(17)

SQL> select * from infinity;

VALUE DESCRIPTION
----- -----------------
   -~ negative infinity
    ~ positive infinity

There has been no supported way to insert these values into a database since Oracle version 6, other than by importing them with data exported from Oracle version 5. Needless to say, there are probably very few databases in which these values are extant. Nevertheless, they make interesting corner cases with which to test the robustness of software intended to work against all Oracle databases. If you would like to see these values for yourself, or use them in your testing, infinity.dmp is a small export file containing just the INFINITY table with the two rows shown above.