B Compatibility Between TimesTen and Oracle Databases

The following sections list compatibility issues between TimesTen and Oracle Databases. The list is not complete, but it indicates areas that require special attention.

Summary of compatibility issues
Transaction semantics
API compatibility
SQL compatibility
Mappings between Oracle Database and TimesTen data types

Summary of compatibility issues

Consider the following differences between TimesTen and Oracle databases:

TimesTen and Oracle database metadata are stored differently. See "API compatibility" for more information.
TimesTen and Oracle databases have different transaction isolation models. See "Transaction semantics" for more information.
TimesTen and Oracle databases have different connection and statement properties. For example, TimesTen does not support catalog names, scrollable cursors or updateable cursors.
Sequences are not cached and synchronized between the TimesTen database and the corresponding Oracle database. See "SQL expressions" for more information.
Side effects of Oracle Database triggers and stored procedures are not reflected in the TimesTen database until after an automatic or manual refresh operation.

Transaction semantics

TimesTen and Oracle Database transaction semantics differ as follows:

Oracle Database serializable transactions can fail at commit time because the transaction cannot be serialized. TimesTen uses locking to enforce serializability.
Oracle Database can provide both statement-level and transaction-level consistency by using a multiversion consistency model. TimesTen does not provide statement-level consistency. TimesTen provides transaction-level consistency by using serializable isolation.
Oracle Database users can lock tables explicitly through SQL. This locking feature is not supported in TimesTen.
Oracle Database supports savepoints while TimesTen does not.
In Oracle Database, a transaction can be set to be read-only or read/write. This is not supported in TimesTen.

For more information about TimesTen isolation levels and transaction semantics, see "Transaction Management and Recovery" in Oracle TimesTen In-Memory Database Operations Guide.

API compatibility

For a complete list of the JDBC API classes and interfaces that TimesTen supports with notes on which methods have a compatibility issue, see "Key JDBC classes and interfaces" in Oracle TimesTen In-Memory Database Java Developer's Guide.

For a complete list of the ODBC API functions that TimesTen supports with notes on which functions have a compatibility issue, see "TimesTen ODBC Functions and Options" in Oracle TimesTen In-Memory Database C Developer's Guide.

For a complete list of the OCI functions that TimesTen supports for Oracle database release 11.2.0.2, see "TimesTen Support for OCI" in Oracle TimesTen In-Memory Database C Developer's Guide.

For information about TimesTen support for Pro*C/C++, see "TimesTen Support for Oracle Pro*C/C++" in Oracle TimesTen In-Memory Database C Developer's Guide.

For information about TimesTen support for ODP.NET, see Oracle Data Provider for .NET Oracle TimesTen In-Memory Database Support User's Guide.

For information about TimesTen support for PL/SQL, see Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

The TimesTen C++ Interface Classes (TTClasses) library provides a high-performance interface to TimesTen that is easy to use. This C++ class library provides wrappers around the most common ODBC functionality. This API is not available for the Oracle Database. See Oracle TimesTen In-Memory Database TTClasses Guide.

SQL compatibility

This section compares TimesTen's SQL implementation with Oracle Database SQL. The purpose is to provide users with a list of Oracle Database SQL features not supported in TimesTen or supported with different semantics.

Schema objects

TimesTen does not recognize some of the schema objects that are supported in Oracle Database. TimesTen returns a syntax error when a statement manipulates or uses these objects. TimesTen passes the statement to Oracle Database. The unsupported objects are:

Clusters
Objects created by the CREATE DATABASE statement
Objects created by the CREATE JAVA statement
Database links
Database triggers
Dimensions
Extended features
External procedure libraries
Index-organized tables
Mining models
Partitions
Object tables, types and views
Operators

TimesTen supports views and materialized views, but it cannot cache an Oracle Database view. TimesTen can cache an Oracle Database materialized view in a user managed cache group without the AUTOREFRESH cache group attribute and PROPAGATE cache table attribute. The cache group must be manually loaded and flushed.

Caching Oracle Database partitioned tables

TimesTen can cache Oracle Database partitioned tables at the table level, but individual partitions cannot be cached. The following describes how operations on partitioned tables affect cache groups:

DDL operations on a table that has partitions do not affect the cache group unless there is data loss. For example, if a partition with data is truncated, an AUTOREFRESH operation does not delete the data from the corresponding cached table.
WHERE clauses in any cache group operations cannot reference individual partitions or subpartitions. Any attempt to define a single partition of a table returns an error.

Nonschema objects

Contexts
Directories
Editions
Restore points
Roles
Rollback segments
Tablespaces

Differences between Oracle Database and TimesTen tables

The Oracle Database table features that TimesTen does not support are:

ON DELETE SET NULL
Check constraints
Foreign keys that reference the table on which they are defined

Data type support

The following Oracle Database data types are not supported by TimesTen:

TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
UROWID
BFILE
Oracle Database-supplied types
User-defined types

The following TimesTen data types are not supported by Oracle Database:

TT_CHAR
TT_VARCHAR
TT_NCHAR
TT_NVARCHAR
TT_BINARY
TT_VARBINARY
TINYINT and TT_TINYINT
TT_SMALLINT
TT_INTEGER
TT_BIGINT
TT_DECIMAL
TT_DATE
TIME and TT_TIME
TT_TIMESTAMP

Note:

TimesTen NCHAR and NVARCHAR2 data types are encoded as UTF-16. Oracle Database NCHAR and NVARCHAR2 data types are encoded as either UTF-16 or UTF-8.

To cache an Oracle Database NCHAR or NVARCHAR2 column, the Oracle Database NLS_NCHAR_CHARACTERSET encoding must be AL16UTF16, not AL32UTF8.

SQL operators

TimesTen supports these operators and predicates that are supported by Oracle Database:

unary -
+, -, *, /
=, <, >, <=, >=, <>, !=
||
IS NULL, IS NOT NULL
LIKE (Oracle Database LIKE operator ignores trailing spaces, but TimesTen does not)
BETWEEN
IN
NOT IN (list)
AND
OR
+ (outer join)
ANY, SOME
ALL (list)
EXISTS
UNION
MINUS
INTERSECT

To perform a bitwise AND operation of two bit vector expressions, TimesTen uses the ampersand character (&) between the expressions while Oracle Database uses the BITAND function with the expressions as arguments.

SELECT statements

TimesTen supports these clauses of a SELECT statement that are supported by Oracle Database:

FOR UPDATE
ORDER BY, including NULLS FIRST and NULLS LAST
GROUP BY, including ROLLUP, GROUPING_SETS and grouping expression lists
Table alias
Column alias
Subquery factoring clause with constructor

Oracle Database supports flashback queries, which are queries against a database that is in some previous state (for example, a query on a table as of yesterday). TimesTen does not support flashback queries.

TimesTen does not support the CONNECT BY clause.

SQL subqueries

TimesTen supports these subqueries that are supported by Oracle Database:

IN (subquery)
>,<,= ANY (subquery)
>,=,< SOME (subquery)
EXISTS (subquery)
>,=,< (scalar subquery)
Subqueries in WHERE clause of DELETE/UPDATE
Subqueries in FROM clause
Subquery factoring clause (WITH constructor)

Note:

A nonverifiable scalar subquery is a scalar subquery whose 'single-row-result-set' property cannot be determined until execution time. TimesTen allows at most one nonverifiable scalar subquery in the entire query and the subquery cannot be specified in an OR expression.

SQL functions

TimesTen supports these functions that are supported by Oracle Database:

ABS
ADD_MONTHS
ASCIISTR
AVG
CAST
CEIL
COALESCE
CONCAT
COUNT
CHR
DECODE
DENSE_RANK
EMPTY_BLOB
EMPTY_CLOB
EXTRACT
FIRST_VALUE
FLOOR
GREATEST
GROUP_ID
GROUPING
GROUPING_ID
INSTR
LAST_VALUE
LEAST
LENGTH
LOWER
LPAD
LTRIM
MAX
MIN
MOD
MONTHS_BETWEEN
NCHR
NLS_CHARSET
NLS_CHARSET_NAME
NLSSORT
NULLIF
NUMTOYMINTERVAL
NUMTODSINTERVAL
NVL
POWER
RANK
REPLACE
ROUND
ROW_NUMBER
RPAD
RTRIM
SIGN
SQRT
SUBSTR
SUM
SYS_CONTEXT
SYSDATE
TO_BLOB
TO_CLOB
TO_CHAR
TO_DATE
TO_LOB
TO_NCLOB
TO_NUMBER
TRIM
TRUNC
UID
UNISTR
UPPER
USER

These TimesTen functions are not supported by Oracle Database:

CURRENT_USER
GETDATE
ORA_SYSDATE
SESSION_USER
SYSTEM_USER
TIMESTAMPADD
TIMESTAMPDIFF
TT_HASH
TT_SYSDATE
TTGRIDNODENAME
TTGRIDMEMBERID
TTGRIDUSERASSIGNEDNAME

TimesTen and the Oracle Database interpret the literal N'\UNNNN' differently. In TimesTen, N'\unnnn' (where nnnn is a number) is interpreted as the national character set character with the code nnnn. In the Oracle Database, N'\unnnn' is interpreted as 6 literal characters. The \u is not treated as an escape. This difference causes unexpected behavior. For example, loading a cache group with a WHERE clause that contains a literal can fail. This can also affects dynamic loading and cache grid operation. Applications should use the UNISTR SQL function instead of literals.

SQL expressions

TimesTen supports these expressions that are supported by Oracle Database:

Column Reference
Sequence
NULL
()
Binding parameters
CASE expression
ROWID pseudocolumn
ROWNUM pseudocolumn

TimesTen and Oracle Database treat literals differently. See the description of HexadecimalLiteral in "Constants" in Oracle TimesTen In-Memory Database SQL Reference.

INSERT/DELETE/UPDATE/MERGE statements

TimesTen supports these DML statements that are supported by Oracle Database:

INSERT INTO ... VALUES
INSERT INTO ... SELECT
UPDATE WHERE expression (expression may contain a subquery)
DELETE WHERE expression (expression may contain a subquery)

TimesTen does not support updating of primary key values except when the new value is the same as the old value.

TimesTen-only SQL and built-in procedures

This section lists TimesTen SQL statements and functions and built-in procedures that are not supported by Oracle Database. With PassThrough=3, these statements are passed to Oracle Database for execution and an error is generated.

All TimesTen cache group DDL and DML statements, including CREATE CACHE GROUP, DROP CACHE GROUP, ALTER CACHE GROUP, LOAD CACHE GROUP, UNLOAD CACHE GROUP, REFRESH CACHE GROUP and FLUSH CACHE GROUP.
All TimesTen replication management DDL statements, including CREATE REPLICATION, DROP REPLICATION, ALTER REPLICATION, CREATE ACTIVE STANDBY PAIR, ALTER ACTIVE STANDBY PAIR and DROP ACTIVE STANDBY PAIR.
FIRST n clause.
ROWS m TO n clause.
All TimesTen built-in procedures. See "Built-In Procedures" in Oracle TimesTen In-Memory Database Reference.

PL/SQL constructs

TimesTen supports a subset of stored procedure constructs, functions, data types, packages and package bodies that are supported by Oracle Database. See Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for details.

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 B-1.

Table B-1 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(m)`	`CHAR(m)`
`VARCHAR2(m)`	`VARCHAR2(m)`
`RAW(m)`	`VARBINARY(m)`
`DATE`	`DATE`
`TIMESTAMP(m)`	`TIMESTAMP(m)`
`NCHAR(m)`	`NCHAR(m)`
`NVARCHAR2(m)`	`NVARCHAR2(m)`

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

Table B-2 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(m)`	`CHAR(m)`
`VARCHAR2(m)`	`VARCHAR2(m)`
`RAW(m)`	`VARBINARY(m)`
`LONG`	`VARCHAR2(m)` Note: `m` can be any valid value within the range defined for the `VARCHAR2` data type.
`LONG RAW`	`VARBINARY(m)` Note: `m` can be any valid value within the range defined for the `VARBINARY` data type.
`DATE`	`DATE` `TIMESTAMP(0)`
`TIMESTAMP(m)`	`TIMESTAMP(m)`
`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(m)`	`NCHAR(m)`
`NVARCHAR2(m)`	`NVARCHAR2(m)`
`CLOB`	`VARCHAR2(n)` Note: 1<=`m`<=4 megabytes.
`BLOB`	`VARBINARY(m)` Note: 1<=`m`<=16 megabytes.
`NCLOB`	`NVARCHAR2(m)` Note: 1<=`m`<=4 megabytes.