NUMERIC data types

Informix® supports several data types to store numbers:

Table 1. Informix numeric data types
Informix data type Description
SMALLINT 16 bit signed integer
INT / INTEGER 32 bit signed integer
BIGINT 64 bit signed integer
INT8 64 bit signed integer (replaced by BIGINT)
DEC / DECIMAL Equivalent to DECIMAL(16)
DEC / DECIMAL(p) Floating-point decimal number
DEC / DECIMAL(p,s) Fixed-point decimal number
MONEY Equivalent to DECIMAL(16,2)
MONEY(p) Equivalent to DECIMAL(p,2)
MONEY(p,s) Equivalent to DECIMAL(p,s)
REAL / SMALLFLOAT 32-bit floating point decimal (C float)
DOUBLE PRECISION / FLOAT[(n)] 64-bit floating point decimal (C double)

ORACLE supports only one data type to store numbers:

Table 2. Oracle numeric data types
ORACLE data type Description
NUMBER(p,s) (1<=p<= 38, -84<=s<=127) Fixed point decimal numbers.
NUMBER(p) (1<=p<= 38) Integer numbers with a precision of p.
NUMBER Floating point decimals with a precision of 38 digits.
FLOAT(b) (1<=b<= 126) Floating point numbers with a binary precision b. This is a sub-type of NUMBER.
BINARY_FLOAT (since Oracle 10g) 32-bit floating point number.
BINARY_DOUBLE (since Oracle 10g) 64-bit floating point number.

ANSI types like SMALLINT, INTEGER are supported by ORACLE but will be converted to the native NUMBER type.

When dividing INTEGERs or SMALLINTs, Informix rounds the result ( 7 / 2 = 3 ), while ORACLE doesn't, because it does not have a native integer data type ( 7 / 2 = 3.5 )

Solution

We recommend that you use the following conversion rules:

Table 3. Conversion rules (Informix vs. Oracle)
Informix data type ORACLE data type (before 10g) ORACLE data type (since 10g)
DECIMAL(p,s), MONEY(p,s) NUMBER(p,s) NUMBER(p,s)
DECIMAL(p) FLOAT(p * 3.32193) FLOAT(p * 3.32193)
DECIMAL (not recommended) FLOAT FLOAT
SMALLINT NUMBER(5,0) NUMBER(5,0)
INTEGER NUMBER(10,0) NUMBER(10,0)
BIGINT NUMBER(20,0) NUMBER(20,0)
INT8 NUMBER(20,0) NUMBER(20,0)
SMALLFLOAT NUMBER BINARY_FLOAT
FLOAT[(p)] NUMBER BINARY_DOUBLE

Avoid dividing integers in SQL statements. If you do divide an integer, use the TRUNC() function with ORACLE.

When creating a table directly in sqlplus, using ANSI data types INTEGER, SMALLINT, you do actually create columns with the native NUMBER type, which has a precision of 38 digits. The NUMBER type cannot be supported by Genero BDL because there is no equivalent type (DECIMAL precision limit is 32 digits, not 38). The same problem exists when using SMALLFLOAT or FLOAT Informix types 4gl in programs doing CREATE TABLE with Oracle versions older than 10g: These Informix types are mapped to NUMBER and cannot be used in 4gl or extracted by fgldbsch (The native Oracle FLOAT(b) type could have been used, but this type is reserved to map DECIMAL(p) Informix types). Starting with Oracle 10g, you can use SMALLFLOAT or FLOAT, these will respectively be converted to BINARY_DOUBLE and BINARY_FLOAT native Oracle types.

When creating a table in a BDL program with DECIMAL (without precision) or with SMALLFLOAT/FLOAT types (if Oracle version is older as 10g), the SQL translator will respectively convert these types to native Oracle FLOAT and NUMBER types, but these types have a higher precision than the Informix / BDL DECIMAL type, thus the fgldbsch schema extractor will fail to extract such columns. Anyway, as a general recommendation, you should not use such floating point numeric types in business applications.