The table provides a description of the supported data types and their limitations.
An administrator or repository builder can use this information to evaluate whether a particular data type is suitable for a given column or set of values, and to determine whether the data type is capable of representing all the required values.
For example, the INTEGER column in the Oracle database supports a very large range of values, up to 38 decimal digits, but the INTEGER data type in Oracle BI EE is a 32-bit binary integer type that is capable of holding up to nine digits without encountering data overflow (truncation) issues. If the column holds values in the range of [-2,147,483,648, 2,147,483,647], then you should use the Oracle BI EE INTEGER data type. However, if the column stores values larger than this range, then you should use another data type such as NUMERIC or VARCHAR.
Choose the smallest, in bytes, data type that is capable of representing the column's expected range of values. Choosing a data type in this way reduces the amount of memory and disk space consumed by the Oracle BI Server for cache files, temp files, and so on.
| Data Type | Limitations |
|---|---|
|
BIG INT |
JDBC and the Oracle BI Administration Tool do not support this type; therefore, Oracle BI EE does not fully support the BIG INT type. The BIG INT type is intended to be same as the C int64 data type. |
|
BINARY |
Oracle BI EE does not fully support the BINARY type. Oracle BI EE supports only the fetching of columns whose data type is BINARY. The BI Server does not support the BINARY type in bind parameters or insert statements. |
|
BIT |
Oracle BI EE does not fully support the BIT type. Instead, you should use either the INT or CHAR type to represent Boolean data. |
|
CHAR |
The CHAR type's values are always padded with ending spaces that can equal up to the length specified by the data type. The CHAR type supports Unicode values. On the Windows platform, the storage is two bytes per character. On all Unix 64-bit platforms, the storage is four bytes per character. |
|
DATE |
The DATE type represents only year, month, and day components. Note that it does not represent hours, minutes, or seconds like the Oracle DATE data type. |
|
DECIMAL |
The DECIMAL type is the same as the NUMERIC type. |
|
DOUBLE |
The DOUBLE type is the same as the IEEE 754 64-bit double-precision binary floating-point data type. The internal storage is eight bytes. The significand occupies 53 bits (including the sign bit). Therefore, the precision is limited to approximately 16 decimal digits. The exponent occupies 11 bits. The range of the exponent is approximately ±307 as a base 10 decimal value. |
|
INTEGER |
The INTEGER type is a signed binary integer data type occupying four bytes. The maximum value that can be represented is 2,147,483,647, and the minimum value is -2,147,483,648. |
|
FLOAT |
The FLOAT type is the same as the IEEE 754 32-bit single-precision binary floating-point data type. The internal storage is four bytes. The significand occupies 24 bits (including the sign bit). Therefore, the precision is limited to approximately 7 decimal digits. The exponent occupies eight bits. The range of the exponent is approximately ±38 as a base 10 decimal value. |
|
LONGVARBINARY |
The LONGVARBINARY type supports up to 32,678 bytes. |
|
LONGVARCHAR |
The LONGVARCHAR type supports up to 32,678 bytes. Both the LONGVARCHAR type and the VARCHAR type support Unicode values. |
|
NUMERIC |
The NUMERIC type is a true decimal data type occupying 22 bytes. The internal representation and limitations are the same as the Oracle NUMBER data type. The NUMERIC type supports positive numbers in the range of 1 x 10^-130 to 9.999...9 x 10^125 with up to 38 significant digits. The precision and scale are not stored in the repository. The scale is assumed to be 10. |
|
REAL |
The REAL type has the same description and limitations as the FLOAT type. |
|
SMALLINT |
The SMALLINT type is represented as the INTEGER type internally in the BI Server and has the same limitations as the INTEGER data type. |
|
TIME |
The TIME type represents only hour, minute, and second components. |
|
TIMESTAMP |
The TIMESTAMP type represents year, month, day, hour, minute, and second components. For some data sources on some platforms, it can also support fractions of a second. |
|
TINYINT |
The TINYINT type is represented as an INTEGER internally in BI Server. The TINYINT type and INTEGER type have the same limitations. |
|
VARBINARY |
The VARBINARY type is interchangeable with the LONGVARBINARY type. The VARBINARY type and the LONGVARBINARY type have the same limitations. |
|
VARCHAR |
The VARCHAR type is interchangeable with the LONGVARCHAR type. The VARCHAR type and LONGCARCHAR type have the same limitations. TheOracle BI Administration Tool allows users to enter a maximum character length of 2,147,483,647. However, the actual maximum length supported is 32,678. |
Some numbers cannot be represented exactly with binary floating point data types such as FLOAT and DOUBLE.
When converting decimal numbers to and from binary floating point representations, often there are rounding errors because of the representational limitations of binary floating point formats. For example, a decimal number such as 1.365 might be represented as 1.364999999999999 when converted to the DOUBLE type. When this number is rounded to 3 digits after the decimal point, the result is 1.365. However, if the number is rounded to 2 decimal digits, then the result is 1.36 and not 1.37.
Oracle BI Server supports the NUMERIC type for RDBMS and TimesTen data sources. To avoid the limitations of the FLOAT and DOUBLE types, Oracle suggests that you update the FLOAT and DOUBLE data types to the NUMERIC type. Note that other than switching to the NUMERIC data type, there is no workaround to fix the inherent limitations with binary floating point data types.