Numeric data types
Informix®
Informix supports several data types to store numbers:
| Informix data type | Description |
|---|---|
SMALLINT |
16 bit signed integer |
INTEGER |
32 bit signed integer |
BIGINT |
64 bit signed integer |
INT8 |
64 bit signed integer (replaced by
BIGINT) |
DECIMAL |
Equivalent to DECIMAL(16) |
DECIMAL(p) |
Floating-point decimal number (max precision is 32) |
DECIMAL(p,s) |
Fixed-point decimal number (max precision is 32) |
MONEY |
Equivalent to DECIMAL(16,2) |
MONEY(p) |
Equivalent to DECIMAL(p,2) (max precision is
32) |
MONEY(p,s) |
Equivalent to DECIMAL(p,s) (max precision is
32) |
REAL / SMALLFLOAT |
32-bit floating point decimal (C float) |
DOUBLE PRECISION / FLOAT[(n)] |
64-bit floating point decimal (C double) |
ORACLE
Oracle® supports following data types to store numbers:
| 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 digits. |
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. |
The type names SMALLINT, INTEGER are supported by Oracle. However, these will be converted to
the native NUMBER(*,0) type. When dividing INTEGER or
SMALLINT types, 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 )
DECIMAL type name is also supported by Oracle, and is mapped to the native NUMBER type. When using
a precision and scale (DECIMAL(p,s)), the resulting NUMBER(p,s)
type is equivalent. However, DECIMAL(p) becomes a NUMBER(p,0), and
DECIMAL without precision / scale becomes a NUMBER(*,0) both
storing whole numbers. This is different from Informix SQL, where a DECIMAL (with
or without precision) can store real numbers.NUMBER
without precision / scale can store real numbers, while Oracle DECIMAL will store
whole numbers. For
example:CREATE TABLE t1 ( num NUMBER, dec DECIMAL );
INSERT INTO t1 VALUES ( 123.456, 123.456 );
SELECT * FROM t1;
NUM DEC
---------- ----------
123.456 123Solution
Use the following conversion rules to map Informix numeric types to Oracle numeric types:
| Informix data type | Oracle data type |
|---|---|
SMALLINT |
NUMBER(5,0) |
INTEGER |
NUMBER(10,0) |
BIGINT |
NUMBER(20,0) |
INT8 |
NUMBER(20,0) |
DECIMAL(p,s) |
NUMBER(p,s) |
DECIMAL(p) |
FLOAT(p * 3.32193) |
DECIMAL (not recommended) |
FLOAT |
MONEY(p,s) |
NUMBER(p,s) |
MONEY(p) |
NUMBER(p,2) |
MONEY |
NUMBER(16,2) |
SMALLFLOAT |
BINARY_FLOAT |
FLOAT[(p)] |
BINARY_DOUBLE |
Avoid dividing integers in SQL statements. If you do divide an integer, use the
TRUNC() function with Oracle.
INTEGER, SMALLINT types, Oracle will create columns with the
native NUMBER(38,0) type. As result, it is not possible (for
fgldbsch) to distinguish the original type names used in CREATE
TABLE from the native NUMBER(38,0) type or NUMBER(38)
type (where scale defaults to zero). In the next example, all columns will be of type
NUMBER(38,0):$ sqlplus ...
sql> CREATE TABLE mytab (
col1 INTEGER,
col2 SMALLINT,
col3 NUMBER(38),
...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])).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.
When casting a numeric expression such as CAST(SUM(col) AS DECIMAL), with
Informix SQL this results in a real numeric value. With Oracle the DECIMAL type becomes a
NUMBER(*,0) and the result is a whole number. The DECIMAL type
name in the CAST() expression is not translated by the Oracle drivers because this
type conversion is only done for DDL statement (CREATE TABLE / ALTER TABLE).
DECIMAL[(p)] or
SMALLFLOAT/FLOAT floating point types in business applications. These types should
only be used for scientific data storage.dbi.database.dsname.ifxemul.datatype.smallint = { true | false }
dbi.database.dsname.ifxemul.datatype.integer = { true | false }
dbi.database.dsname.ifxemul.datatype.bigint = { true | false }
dbi.database.dsname.ifxemul.datatype.int8 = { true | false }
dbi.database.dsname.ifxemul.datatype.decimal = { true | false }
dbi.database.dsname.ifxemul.datatype.money = { true | false }
dbi.database.dsname.ifxemul.datatype.float = { true | false }
dbi.database.dsname.ifxemul.datatype.smallfloat = { true | false }