Caching Oracle Database LOB Data

You can cache Oracle Database large object (LOB) data in cache groups in TimesTen.

TimesTen caches the data as follows:

• Oracle Database CLOB data is cached as TimesTen VARCHAR2 data.

• Oracle Database BLOB data is cached as TimesTen VARBINARY data.

• Oracle Database NCLOB data is cached as TimesTen NVARCHAR2 data.

The following example shows how to cache Oracle Database LOB data

1. Create a table in the Oracle database that has LOB fields.

   CREATE TABLE t (
     i INT NOT NULL PRIMARY KEY
     , c CLOB
     , b BLOB
     , nc NCLOB);

2. Insert values into the Oracle Database table. The values are implicitly converted to TimesTen VARCHAR2, VARBINARY, OR NVARCHAR2 data types.

   INSERT INTO t VALUES (1
     , RPAD('abcdefg8', 2048, 'abcdefg8')
     , HEXTORAW(RPAD('123456789ABCDEF8', 4000, '123456789ABCDEF8'))
     , RPAD('abcdefg8', 2048, 'abcdefg8')
   );
   
   1 row inserted.

3. Create a dynamic AWT cache group and start the replication agent.

   CREATE DYNAMIC ASYNCHRONOUS WRITETHROUGH CACHE GROUP cg1 
     FROM t
    (i INT NOT NULL PRIMARY KEY
     , c VARCHAR2(4194303)
     , b VARBINARY(4194303)
     , nc NVARCHAR2(2097152));
   
   CALL ttrepstart;

4. Load the data dynamically into the TimesTen cache group.

   SELECT * FROM t WHERE i = 1;
   
   I:    1
   C:    abcdefg8abcdefg8abcdefg8...
   B:    123456789ABCDEF8123456789...
   NC:   abcdefg8abcdefg8abcdefg8...
   
   1 row found.

Restrictions on Caching Oracle Database LOB Data

There are restrictions when caching Oracle Database LOB data into TimesTen.

These restrictions apply to caching Oracle Database LOB data in TimesTen cache groups:

• Column size is enforced when a cache group is created. VARBINARY, VARCHAR2 and NVARCHAR2 data types have a size limit of 4 megabytes. Values that exceed the user-defined column size are truncated at run time without notification.

• Empty values in fields with CLOB and BLOB data types are initialized but not populated with data. Empty CLOB and BLOB fields are treated as follows:

  • Empty LOB fields in the Oracle database are returned as NULL values.

  • Empty VARCHAR2 and VARBINARY fields in TimesTen are propagated as NULL values.

In addition, cache groups that are configured for autorefresh operations have these restrictions on caching LOB data:

• When LOB data is updated in the Oracle database by OCI functions or the DBMS_LOB PL/SQL package, the data is not automatically refreshed in the cache group in TimesTen. This occurs because TimesTen caching operations depend on Oracle Database triggers, and Oracle Database triggers are not processed when these types of updates occur. TimesTen does not notify the user that updates have occurred without being refreshed in TimesTen. When the LOB data is updated in the Oracle database through a SQL statement, a trigger is fired and autorefresh brings in the change.

• Since autorefresh operations always refresh entire rows, LOB data in the cache is updated when any other column in the same row is updated.