Oracle Select Statements Return Numeric Literals
SELECT statements that contain a numeric literal sent to the Oracle gateway will return a floating point number. However, if you typecast the statement to an integer, it will return a numeric literal—and be consistent across multiple gateways.
This behavior also affects most functions that return numeric data, for example SUM(), and MOD().
The OPENSQL_ORACLE_NUMBER_MAPPING setting, defined at gateway catalog creation time for number mapping, has no effect. The behavior detailed in this section results from the way Oracle handles numeric data.
For example, assuming the following table and data:
create table inttest (col1 int, col2 varchar (20));
insert into inttest (col1,col2) values (1, '1');
insert into inttest (col1,col2) values (2, '2');
Then the following SELECT statements return floating point from Oracle:
select sum(col1) from inttest;
select 1 from iidbconstants;
Examples
The following statement selects a numeric literal through the Oracle gateway, which returns a floating point:
* select 1 from iidbconstants;\p\g
/* SQL Startup File */
select 1 from iidbconstants;
Executing . . .
+-----------+
|1 |
+-----------+
| 1.000|
+-----------+
(1 row)
The following statement selects an integer4 as an explicit typecast, and the Oracle gateway returns a numeric literal:
* select int4(1) as explict_cast from iidbconstants;\p\g
select int4(1) as explict_cast from iidbconstants;
Executing . . .
+-------------+
|EXPLICT_CAST |
+-------------+
| 1|
+-------------+
(1 row)
This statement selects an integer8 as an explicit typecast, and the Oracle gateway returns a numeric literal:
* select int8(1) as explict_cast from iidbconstants;\p\g
select int8(1) as explict_cast from iidbconstants;
Executing . . .
+----------------------+
|EXPLICT_CAST |
+----------------------+
| 1|
+----------------------+
(1 row)