C 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
There are a few compatibility issues between the TimesTen and Oracle databases.
Consider the following differences between TimesTen and Oracle databases:
-
TimesTen and Oracle database metadata are stored differently. See API Compatibility.
-
TimesTen and Oracle databases have different transaction isolation models. See Transaction Semantics.
-
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.
-
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 in a few ways.
-
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 multi-version consistency model. TimesTen does not provide statement-level consistency. TimesTen provides transaction-level consistency by using serializable isolation.
-
Oracle Database users can lock tables manually 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.
See Transaction Management in Oracle TimesTen In-Memory Database Operations Guide.
API Compatibility
There are methods from the JDBC and ODBC APIs that have a compatibility issue with cache.
The following sections list methods from the JDBC and ODBC APIs that have a compatibility issue with cache.
JDBC API Compatibility
There are compatibility issues that apply to the JDBC API.
Compatibility issues that apply to JDBC include the following:
-
JDBC database metadata functions return TimesTen metadata. If you want Oracle metadata, connect to the Oracle Database directly.
-
The set/get connection and statement attributes are performed on TimesTen.
-
All Oracle
java.sql.ResultSet
metadata (length, type, label) is returned in TimesTen data type lengths. The column labels that are returned are TimesTen column labels. -
Oracle extensions (
oracle.sql
andoracle.jdbc
packages) are not supported. -
Java stored procedures are not supported in TimesTen.
java.sql.Connection
The following Connection
methods have no compatibility issues:
close() commit() createStatement() prepareCall() prepareStatement() rollback() setAutoCommit()
The following methods are run locally in TimesTen:
getCatalog() getMetaData get/setTransactionIsolation() isReadOnly() isClosed() nativeSQL() setCatalog() setReadOnly()
Note:
See Transaction Semantics for restrictions for the
get/setTransactionIsolation()
methods.
The isClosed()
method returns only the TimesTen connection status.
java.sql.Statement
The following Statement
methods have no compatibility issues:
addBatch() clearBatch() close() execute() executeBatch() executeQuery() executeUpdate() getResultSet() getUpdateCount() getWarnings()
The following methods run locally in TimesTen:
cancel() get/setMaxFieldSize() get/setMaxRows() get/setQueryTimeout() getMoreResults() setEscapeProcessing() setCursorName()
java.sql.ResultSet
The following ResultSet
methods have no compatibility issues:
close() findColumn(int) and findColumn(string) getXXX(number) and getXXX(name) getXXXStream(int) and getXXXStream(string) getMetaData()
java.sql.PreparedStatement
The following PreparedStatement
methods have no compatibility issues:
addBatch() close() execute() executeUpdate() executeQuery() getResultSet() getUpdateCount() setXXX() setXXXStream()
The following methods run locally in TimesTen:
cancel() get/setMaxFieldSize() get/setMaxRows() get/setQueryTimeout() getMoreResults() setEscapeProccessing() setCursorName()
java.sql.CallableStatement
The same restrictions as shown for the java.sql.Statement
and java.sql.PreparedStatement
interfaces apply to CallableStatement
.
-
In a
WRITETHROUGH
cache group, ifPassThrough
=1
, indirect DML operations that are hidden in stored procedures or induced by triggers may be passed through without being detected by Cache Connect to Oracle. -
Stored procedures that update, insert, or delete from
READONLY
cache group tables will be autorefreshed within another transaction in an asynchronous fashion. Thus, the changes do not appear within the same transaction that the stored procedure was processed within and there may be some time lapse before the changes are autorefreshed into the cache table.
java.sql.ResultSetMetaData
The following ResultSetMetaData
methods have no compatibility issues:
getColumnCount() getColumnType() getColumnLabel() getColumnName() getTableName() isNullable()
The following methods run locally in TimesTen:
getSchemaName() getCatalogName() getColumnDisplaySize() getColumnType() getColumnTypeName() getPrecision() getScale() isAutoIncrement() isCaseSensitive() isCurrency() isDefinitelyWritable() isReadOnly() isSearchable() isSigned() isWritable()
Stream Support
There are compatibility issues related to streams.
The compatibility issues related to streams are:
-
The JDBC driver fully fetches the data into an in-memory buffer during a call to the
executeQuery()
ornext()
methods. ThegetXXXStream()
entry points return a stream that reads data from this buffer. -
Oracle supports up to 2 GB of long or long raw data. When cached, TimesTen converts
LONG
data intoVARCHAR2
data. TimesTen convertsLONG RAW
data intoVARBINARY
data. BothVARCHAR2
andVARBINARY
data types can store up to a maximum 4,194,304 (222) bytes). -
Oracle always streams
LONG
/LONG RAW
data even if the application does not callgetXXXStream()
. -
TimesTen does not support the
mark()
,markSupported()
, andreset()
methods.
ODBC API Compatibility
Cache in TimesTen is compatible with a subset of ODBC functions.
Table C-1 describes the compatibility of ODBC functions.
Table C-1 ODBC Function Compatibility With Cache in TimesTen
Function Name | Compatibility |
---|---|
|
Default TimesTen behavior matches Oracle Database behavior. See Binding Parameters and Running Statements in Oracle TimesTen In-Memory Database C Developer's Guide. |
|
Not supported. |
|
There are some restrictions. In particular, |
|
There are some restrictions. See the |
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 sub-partitions. Any attempt to define a single partition of a table returns an error.
Non-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:
- Contexts
- Directories
- Editions
- Restore points
- Roles
- Rollback segments
- Tablespaces
Differences Between Oracle Database and TimesTen Tables
TimesTen supports a subset of the Oracle Database features.
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
Certain 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
andTT_TINYINT
TT_SMALLINT
TT_INTEGER
TT_BIGINT
TT_DECIMAL
TT_DATE
TIME
andTT_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 a subset of operators and predicates that are supported by the Oracle Database:
- unary
-
+, -, *, /
-
=, <, >, <=, >=, <>, !=
||
IS NULL
,IS NOT NULL
LIKE
(Oracle DatabaseLIKE
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 run a bitwise
AND
operation of two bit vector expressions, TimesTen uses the ampersand character (&
) between the expressions while Oracle Database uses theBITAND
function with the expressions as arguments.
SELECT Statements
TimesTen supports a subset of clauses of a SELECT
statement that are
supported by the Oracle Database:
-
FOR UPDATE
-
ORDER BY
, includingNULLS FIRST
andNULLS LAST
-
GROUP BY
, includingROLLUP
,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.
SQL Subqueries
TimesTen supports a subset of subqueries that are supported by the Oracle Database.
IN
(subquery)>,<,= ANY
(subquery)>,=,< SOME
(subquery)EXISTS
(subquery)>,=,<
(scalar subquery)- Subqueries in
WHERE
clause ofDELETE
/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 runtime. 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 a subset of functions that are supported by the 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
TimesTen and the Oracle Database interpret the literal N'\UNNNN
' differently. In TimesTen, N'\u
nnnn
'
(where nnnn
is a number) is interpreted as the national character set character with the code nnnn
. In the Oracle Database, N'\u
nnnn
'
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. Applications should use the UNISTR
SQL function instead of literals.
SQL Expressions
TimesTen supports a subset of expressions that are supported by the Oracle Database.
- Column Reference
- Sequence
NULL
()
- Binding parameters
CASE
expressionROWID
pseudocolumnROWNUM
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 certain DML statements that are also supported by the 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
There are TimesTen SQL statements and functions and built-in procedures that are not supported by the Oracle Database.
With PassThrough
=3, these statements are passed to
Oracle Database for processing 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
andFLUSH CACHE GROUP
. -
All TimesTen replication management DDL statements, including
CREATE REPLICATION
,DROP REPLICATION
,ALTER REPLICATION
,CREATE ACTIVE STANDBY PAIR
,ALTER ACTIVE STANDBY PAIR
andDROP 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.
-
TimesTen specific syntax for character and unicode strings are not always converted to the Oracle Database syntax when using
PassThrough
=3.Note:
For more details on TimesTen support for unicode strings, see Character and Unicode Strings in the Oracle TimesTen In-Memory Database Reference.
-
Supplying
\046
converts to the&
symbol on TimesTen, but is not converted to this symbol when passed through to an Oracle database. The\
xyz
notation is not supported by the Oracle database. To send a character through to an Oracle database, pass it as an argument within theCHR()
function with the decimal value of the character. -
TimesTen enables depicting a unicode value (a four-digit hexadecimal number) within a character string with the
\u
xyzw
syntax (forNCHAR
andNVARCHAR2
only) where you substitute the unicode value forxyzw
, as in\ufe4a
.The
\u
xyzw
notation is not supported by the Oracle database. Thus, any unicode strings inNCHAR
orNVARCHAR2
columns passed through to an Oracle database must be passed as an argument within theUNISTR()
function without theu
character.The following example inserts the unicode values
'0063'
and'0064'
, which are thea
andb
characters respectively. Since we are usingPassThrough
=3, this statement is performed on the Oracle database; thus, we do not provide theu
character as we would if this was performed on TimesTen.Command> INSERT INTO my_tab VALUES (UNISTR(n'\0063\0064')); 1 row inserted.
-
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 Overview of PL/SQL Features in the Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
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 |
---|---|
|
Note: |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
Where |
|
Where |
|
|
|
|
Note: Includes |
Note: |
|
|
|
|
|
|
|
|
|
Where 1 <= |
|
Where 1 <= |
|
Where 1 <= |