Oracle database 12.1.0.2
I've got a table with some data which was created by a third party java application. One of the columns is of type integer and seems to contain a value of infinity. My understanding is that Integer can store whole numbers in the range of (-2**31) to (2**31)-1.
SQL> describe test_number
Name Null? Type
------------ -------- ------------
ID NUMBER(38)
SQL> select * from test_number;
ID
----------
~
1 row selected.
SQL> select dump(id, 16) from test_number;
DUMP(ID,16)
----------------------
Typ=2 Len=2: ff,65
1 row selected.
Selecting the value in SQL*Developer I get "Infinity".
We think the java application created this value by dividing 1 by zero. But I can't reproduce it. I can create a table with an infinity BINARY_FLOAT value like this:
SQL> create table test_number2 as (select 1/0f as id from dual);
Table created.
SQL> desc test_number2
Name Null? Type
------------ -------- --------------
ID BINARY_FLOAT
SQL> select * from test_number2;
ID
----------
Inf
1 row selected.
But I can't get infinity into an integer type:
SQL> create table test_number3 (id integer);
Table created.
SQL> insert into test_number3 values (1/0f);
insert into test_number3 values (1/0f)
*
ERROR at line 1:
ORA-01426: numeric overflow
SQL> insert into test_number3 values (to_number(1/0f));
insert into test_number3 values (to_number(1/0f))
*
ERROR at line 1:
ORA-01426: numeric overflow
I'm curious to know how infinity can be stored in an integer column, and how can it be inserted using SQL.
Thanks,
John