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(*,s)	Fixed point decimal numbers with a precision of 38 digits.
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 Oracle's sqlplus, using ANSI data types INTEGER, SMALLINT, you do actually create columns with the NUMBER type, which has a precision of 38 digits. As result, it is not possible to distinguish the original types used in CREATE TABLE, nor can it be possible to distinguish the columns created explicitely with the native NUMBER type, in the next example, all column with be of type NUMBER:

$ sqlplus ...
sql> CREATE TABLE mytab (
   col1 INTEGER,
   col2 SMALLINT,
   col3 NUMBER,
   ...

When extracting the database schema with fgldbsch, NUMBER, NUMBER(p>32) and NUMBER(p>32,s) types will by default give an extraction error. However, these types can be converted to DECIMAL(32) and DECIMAL(32,s) with the -cv option, by using the "B" character at positions 22 (for NUMBER) and 23 (for NUMBER(p>32[,s])).

Note: When fetching a NUMBER[(p>32,s)] into a BDL DECIMAL(32[,s]) type, if the value stored in the NUMBER column has more than 32 digits, it will be rounded to fit into a DECIMAL(32), or the overflow error -1226 will occur when fetching into a DECIMAL(32,s). Note that it must be allowed to fetch numeric expressions such as 1/3 (=0.333333333333....) into a DECIMAL(p,s), even if such expression will produce more than 32 digits with Oracle.

When creating a table in a BDL program with DECIMAL(p), this type is converted to native Oracle FLOAT(p*3.32193). When creating a table in a BDL program with DECIMAL (without precision) this type is converted to native Oracle FLOAT. The native Oracle FLOAT[(p)] type can be extracted by fgldbsch, but Oracle's FLOAT has a higher precision than the BDL DECIMAL type, which can lead to value rounding when fetching rows.

With Oracle versions older than 10g, when creating tables in a BDL program with SMALLFLOAT or FLOAT types, these types are mapped to NUMBER (The native Oracle FLOAT(b) type could have been used, but this type is reserved to map DECIMAL(p) types). Starting with Oracle 10g, SMALLFLOAT or FLOAT types will respectively be converted to BINARY_FLOAT and BINARY_DOUBLE native Oracle types, which can be extracted by fgldbsch and mapped back to BDL SMALLFLOAT and FLOAT respectively in the .sch file.

Note: As a general recommendation, do not use DECIMAL[(p)] or SMALLFLOAT/FLOAT floating point types in business applications. These types should only be used for scientific data storage.