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.