This appendix lists and describes the data types supported by Oracle BI EE. This appendix contains information about the data type limitations, other Oracle BI Server limitations, and floating point limitations. It also contains instructions for using the Oracle BI Server nqcmd utility to run the NQSGetSQLDataTypes procedure to obtain information about the data types.
When you import metadata from a data source into the repository's physical layer, each column is assigned a data type. The data type is associated with a specific storage format, constraints, and a valid range of values.
This appendix contains the following topics:
This topic lists the data types by category (for example, numeric data and date data) that Oracle BI EE supports. See "Using the NQSGetSQLDataTypes Procedure to Access Data Type Information" and "Oracle BI EE Data Type Limitations" for information about specific data types.
Oracle BI EE supports the following numeric data types:
BIGINT
DECIMAL
DOUBLE
FLOAT
INTEGER
NUMERIC
REAL
SMALLINT
TINYINT
Oracle BI EE supports the following data and time data types:
DATE
TIME
TIMESTAMP
To access a list of data types supported by Oracle BI EE, use the Oracle BI Server utility nqcmd to run the NQSGetSQLDataTypes procedure. For example: call NQSGetSQLDataTypes(0);
When you run this procedure, the results contain a list of supported data types and information specific to each data type, such as case sensitivity and searchability.
See "Using nqcmd to Test and Refine the Repository" for more information about opening and using the Oracle BI Server nqcmd utility.
The following table contains each supported data type and its 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, which 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 like NUMERIC or even 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 BI Server for cache files, temp files, and so on.
Table F-1 Data Type Limitations
| Data Type | Limitations |
|---|---|
|
BIG INT |
JDBC and the Administration Tool do not support this type; therefore, Oracle BI EE does not fully support the BIG INT type. BI Server does offer some support for this type, but BIG INT has not been thoroughly tested with Oracle BI Server. 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. BI Server 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 |
|
|
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. See "Floating Point Limitations" for more information about the limitations of this type. |
|
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. See "Floating Point Limitations" for more information about the limitations of this type. |
|
LONGVARBINARY |
|
|
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 10125 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. The 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.
In addition to the data type limitations, Oracle BI Server has the following limitations:
The default maximum length of all fields in Oracle BI Server is 32,678 bytes. This default limit can be changed by setting the environment variable OBIS_MAX_FIELD_SIZE.
The default maximum length of all SQL identifiers (for example, table names and column names) is 128 characters.
See "Oracle BI EE Data Type Limitations" for information about supported data types and their limitations.
When you import metadata from an Oracle database, the Administration Tool uses the mapping in Table F-2 to determine each imported column's corresponding Oracle BI Server data type. Note that how the data types map to the Oracle BI EE data types differs depending on the kind of database that you are using.
For more information about Oracle Database data types, see "Chapter 26 Oracle Data Types" in Oracle Database Concepts.
| Oracle Database Data Type | Oracle BI EE Data Type |
|---|---|
|
CHAR |
CHAR |
|
NCHAR |
CHAR |
|
VARCHAR2 |
VARCHAR |
|
NVARCHAR2 |
VARCHAR |
|
NUMBER |
NUMERIC if ENABLE_NUMERIC_DATA_TYPE = YES; otherwise, DOUBLE |
|
NUMBER (precision, scale) |
INT if scale = 0 and 1 <= precision <= 9; otherwise, same as NUMBER |
|
BINARY_FLOAT |
FLOAT |
|
BINARY_DOUBLE |
DOUBLE |
|
DATE |
DATETIME |
|
TIMESTAMP |
TIMESTAMP |
|
TIMESTAMP WITH TIME ZONE |
TIMESTAMP |
|
TIMESTAMP WITH LOCAL TIME ZONE |
TIMESTAMP |
|
BLOB |
LONGVARBINARY |
|
CLOB |
LONGVARCHAR |
|
NCLOB |
LONGVARCHAR |
|
BFILE |
Not supported |
|
LONG |
LONGVARCHAR |
|
LONG RAW |
Not supported |
|
ROWID |
CHAR |
|
XML Type |
LONGVARBINARY |
|
UriType |
Not supported |