TimesTen PL/SQL with Cache

When PL/SQL programs execute SQL statements, the SQL statements are processed by TimesTen in the same manner as when SQL is executed from applications written in other programming languages. All standard behaviors of TimesTen SQL apply. In a cache environment, this includes the ability to use all cache features from PL/SQL. When PL/SQL accesses tables in cache groups, the same rules for those tables apply. For example, issuing a SELECT statement against a cache instance in a dynamic cache group may cause the instance to be automatically loaded into TimesTen from Oracle Database.

In particular, be aware of the following points about this functionality.

When you use static SQL in PL/SQL, any tables accessed must exist in TimesTen or the PL/SQL will not compile successfully. In the following example, ABC must exist in TimesTen.
```
begin
  insert into abc values(1, 'Y');
end;
```
In a cache environment, there is the capability to use the TimesTen passthrough facility to automatically route SQL statements from TimesTen to Oracle Database. (See Setting a Passthrough Level in Oracle TimesTen In-Memory Database Cache Guide for details of the passthrough facility.)

With passthrough=1, a statement can be passed through to Oracle Database if any accessed table does not exist in TimesTen. In PL/SQL, however, the statement would have to be executed using dynamic SQL.

Updating the preceding example, the following TimesTen PL/SQL block could be used to access ABC in Oracle Database with passthrough=1:
```
begin
  execute immediate 'insert into abc values(1, 'Y')';
end;
```
In this case, TimesTen PL/SQL can compile the block because the SQL statement is not examined at compile time.
While PL/SQL can be executed in TimesTen, the TimesTen passthrough facility cannot be used to route PL/SQL blocks from TimesTen to Oracle Database. For example, when using cache with passthrough=3, statements executed on a TimesTen connection are routed to Oracle Database in most circumstances. In this scenario, you may not execute PL/SQL blocks from your application program, because TimesTen would attempt to forward them to Oracle Database, which is not supported. (In the passthrough=1 example, it is just the SQL statement being routed to Oracle Database, not the block as a whole.)

Tip:

PL/SQL procedures and functions can use any of the following cache operations with either definer's rights or invoker's rights:

Loading or refreshing a cache group with commit every n rows
DML on AWT cache groups
DML on non-propagated cache groups (user managed cache groups without PROPAGATE enabled)
SELECT on cache group tables that do not invoke passthrough or dynamic load
UNLOAD CACHE GROUP

PL/SQL procedures or functions that use any of the following cache operations must use invoker's rights (AUTHID CURRENT_USER): passthrough, dynamic loading of a cache group, loading or refreshing a cache group using WITH ID, DDL on cache groups, DML on SWT cache groups, or FLUSH CACHE GROUP.

See Definer's Rights and Invoker's Rights (AUTHID Clause) in Oracle TimesTen In-Memory Database Security Guide.