PassThrough
It specifies which SQL statements are executed only in the cache database and which SQL statements are passed through to the Oracle database. For more information see Setting a Passthrough Level in the Oracle TimesTen In-Memory Database Cache Guide and CREATE CACHE GROUP in Oracle TimesTen In-Memory Database SQL Reference.
The execution of a prepared PassThrough
command assumes that the schema of dependent objects in the Oracle database has not changed since the prepare. If the schema has changed the PassThrough
command may cause unexpected results from the Oracle database.
When passing SQL statements through to the Oracle database, use only TimesTen supported data types in column definitions. If the specified data type is not supported in TimesTen, the passthrough statement fails.
For information on changing the isolation level on the Oracle database connection, when using this attribute, see Isolation.
Required Privilege
No privilege is required to change the value of this attribute.
Usage in TimesTen Scaleout and TimesTen Classic
This attribute is supported in both TimesTen Classic and TimesTen Scaleout.Setting
Set PassThrough
as follows.
Where to set the attribute | How the attribute is represented | Setting |
---|---|---|
C or Java programs or UNIX and Linux systems |
|
|
Windows ODBC Data Source Administrator |
Not applicable |
Restrictions
Certain restrictions must be considered when using the passthrough feature. They include:
-
If the
PassThrough
attribute is set so that a query must be executed in the Oracle database, the query is sent to the Oracle database without any changes. If the query uses a synonym for a table in a cache group, then a synonym with the same name must be defined for the corresponding Oracle database table for the query to be successful. -
In the case that a SQL statement that uses TimesTen only syntax is passed through to the Oracle database, TimesTen returns an error message that indicates the syntax is not supported in the Oracle database.
-
Execution of a prepared passthrough command assumes that the schema of dependent objects in the Oracle database have not changed after the prepare. If the schema has changed, unexpected results can occur.
-
TimesTen does not include a cache invalidation feature. TimesTen does not verify that the cached tables are up to date. When a query is syntactically correct in TimesTen and the cache contains all the tables referenced in the query, the query is executed in TimesTen regardless of whether the cache is up to date.
-
The passthrough of Oracle
INSERT
,UPDATE
, orDELETE
operations depends on the setting of thePassThrough
attribute as described in the table above. TimesTen Cache cannot detectINSERT
,UPDATE
andDELETE
operations that are hidden in a trigger or stored procedure. Therefore, TimesTen cannot enforce the passthrough rule on hidden operations. -
You cannot pass PL/SQL blocks through to the Oracle database.
-
The effects of a passthrough
INSERT
,UPDATE
, orDELETE
operation on a read-only cache group are only seen after the transaction is committed and after the next autorefresh operation is completed. -
There is no mechanism to detect or block updates on an Oracle database table that is cached in a TimesTen synchronous writethrough cache group. Whether the updates are made by statements passed through the cache or from other Oracle database applications, the changes are never reflected in TimesTen.
-
Oracle Call Interface (OCI) does not support a mechanism to describe the binding type of the input parameters. Ensure that your application supplies the correct SQL types for passthrough statements. The ODBC driver converts the C and SQL types and presents the converted data and the SQL type code to TimesTen. TimesTen presents the information to OCI. The length of the input binding values is restricted to 4000 for
LONG
andLONG RAW
types. -
At all passthrough levels, passthrough execution of DDL statements does not result in commits on the TimesTen side.
-
A transaction that contains operations that are replicated with
RETURN TWOSAFE
cannot have aPassThrough
setting greater than0
. IfPassThrough
is greater than0
, an error is returned and the transaction must be rolled back. -
When
PassThrough
is set to0
,1
, or2
, the following behavior occurs when a dynamic load condition exists:-
A dynamic load can occur for a
SELECT
operation on cache tables in any dynamic cache group type. -
A dynamic load for an
INSERT
,UPDATE
, orDELETE
operation can only occur on cached tables with dynamic asynchronous or synchronous writethrough cache groups.
-
Refer to SQL Statements in Oracle TimesTen In-Memory Database SQL Reference for details about the INSERT
, UPDATE
, DELETE
, and SELECT
statements.