Mappings Between Oracle Database and TimesTen Data Types

When you choose data types for columns in the TimesTen cache tables, consider the data types of the columns in the Oracle Database tables and choose an equivalent or compatible data type for the columns in the cache tables.

Note:

TimeTen cache, including passthrough, does not support the Oracle Database ROWID data type. However, you can cast a ROWID data type to a CHAR(18) when provided on the SELECT list in a SQL query.

The following example demonstrates the error that is returned when you do not cast the ROWID data type. Then, the example shows the correct casting of a ROWID data type to CHAR(18):

Command> SET PASSTHROUGH 3;
Passthrough command has set autocommit off.
Command> SELECT ROWID FROM dual;
 5115: Unsupported type mapping for column ROWID
The command failed.
Command> SELECT CAST (ROWID AS CHAR(18)) FROM DUAL;
< AAAAB0AABAAAAEoAAA >
1 row found.

Primary and foreign key columns are distinguished from non-key columns. The data type mappings allowed for key columns in a cache table are shown in Table C-2.

Table C-2 Data Type Mappings Allowed for Key Columns

Oracle Database Data Type	TimesTen Data Type
`NUMBER(p`,`s)`	`NUMBER(p,s)` Note: `DECIMAL(p,s)` or `NUMERIC(p,s)` can also be used. They are aliases for `NUMBER(p,s)`.
`NUMBER(p`,`0)` `INTEGER`	`TT_TINYINT` `TT_SMALLINT` `TT_INTEGER` `TT_BIGINT` `NUMBER(p`,`0)`
`NUMBER`	`TT_TINYINT` `TT_SMALLINT` `TT_INTEGER` `TT_BIGINT` `NUMBER`
`CHAR(n)`	`CHAR(n)`
`VARCHAR2(n)`	`VARCHAR2(n)`
`RAW(n)`	`VARBINARY(n)`
`DATE`	`DATE`
`TIMESTAMP(n)`	`TIMESTAMP(n)`
`NCHAR(n)`	`NCHAR(n)`
`NVARCHAR2(n)`	`NVARCHAR2(n)`

Table C-3 shows the data type mappings allowed for non-key columns in a cache table.

Table C-3 Data Type Mappings Allowed for Non-Key Columns

Oracle Database Data Type	TimesTen Data Type
`NUMBER(p`,`s)`	`NUMBER(p`,`s)` `REAL` `FLOAT` `BINARY_FLOAT` `DOUBLE` `BINARY_DOUBLE`
`NUMBER(p`,`0)` `INTEGER`	`TT_TINYINT` `TT_SMALLINT` `TT_INTEGER` `TT_BIGINT` `NUMBER(p`,`0)` `FLOAT` `BINARY_FLOAT` `DOUBLE` `BINARY_DOUBLE`
`NUMBER`	`TT_TINYINT` `TT_SMALLINT` `TT_INTEGER` `TT_BIGINT` `NUMBER` `REAL` `FLOAT` `BINARY_FLOAT` `DOUBLE` `BINARY_DOUBLE`
`CHAR(n)`	`CHAR(n)`
`VARCHAR2(n)`	`VARCHAR2(n)`
`RAW(n)`	`VARBINARY(n)`
`LONG`	`VARCHAR2(n)` Where `n` can be any valid value within the range defined for the `VARCHAR2` data type.
`LONG RAW`	`VARBINARY(n)` Where `n` can be any valid value within the range defined for the `VARBINARY` data type.
`DATE`	`DATE` `TIMESTAMP(0)`
`TIMESTAMP(n)`	`TIMESTAMP(n)`
`FLOAT(n)` Note: Includes `DOUBLE` and `FLOAT`, which are equivalent to `FLOAT(126)`. Also includes `REAL`, which is equivalent to `FLOAT(63)`.	`FLOAT(n)` `BINARY_DOUBLE` Note: `FLOAT(126)` can be declared as `DOUBLE. FLOAT(63)` can be declared as `REAL`.
`BINARY_FLOAT`	`BINARY_FLOAT`
`BINARY_DOUBLE`	`BINARY_DOUBLE`
`NCHAR(n)`	`NCHAR(n)`
`NVARCHAR2(n)`	`NVARCHAR2(n)`
`CLOB`	`VARCHAR2(n)` Where 1 <= `n` <= 4 MB.
`BLOB`	`VARBINARY(n)` Where 1 <= `n` <= 4 MB.
`NCLOB`	`NVARCHAR2(n)` Where 1 <= `n` <= 2 MB.