| SQL adaptation guide For Oracle Database 9.2, 10.x, 11.x, 12x / Data dictionary | |
Informix® supports several data types to store numbers:
| 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:
| 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 )
We recommend that you use the following conversion rules:
| 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.