1. C Developer's Guide
  2. TimesTen Support for OCI
  3. TimesTen Features With OCI

TimesTen Features With OCI

This section covers the following topics for developers using TimesTen OCI.

TimesTen Deferred Prepare

In OCI, a prepare call is expected to be a lightweight operation performed on the client.

To enable TimesTen to be consistent with this expectation, and to avoid unwanted round trips between client and server, the TimesTen client library implementation of SQLPrepare performs what is referred to as a deferred prepare, where the request is not sent to the server until required. See TimesTen Deferred Prepare.

Parameter Binding Features in TimesTen OCI

There are features relating to binding parameters into SQL or PL/SQL from an OCI application.

Binding Duplicate Parameters in TimesTen OCI

In TimesTen OCI, as in ODBC, multiple occurrences of the same parameter name are considered to be distinct parameters. However, OCI allows multiple occurrences to be bound with a single call to OCIBindByPos().

See Binding of Duplicate Parameters in SQL Statements.

Consider this query:

SELECT * FROM employees
  WHERE employee_id < :a AND manager_id > :a AND salary < :b;

The two occurrences of parameter a are considered to be separate parameters, but you have the option of binding both occurrences with a single call to OCIBindByPos(): 

OCIBindByPos(..., 1, ...); /* both occurrences of :a */
OCIBindByPos(..., 3, ...); /* occurrence of :b */

Or you can bind the two occurrences of a separately: 

OCIBindByPos(..., 1, ...); /* first occurrence of :a */
OCIBindByPos(..., 2, ...); /* second occurrence of :a */
OCIBindByPos(..., 3, ...); /* occurrence of :b */

Note that in both cases, parameter b is considered to be in position 3.

Note:

OCI also allows parameters to be bound by name, rather than by position, using OCIBindByName(). In this case, the same value is used for any parameters that have the same name.

Binding Associative Arrays in TimesTen OCI

Associative arrays, formerly known as index-by tables or PL/SQL tables, are supported as IN, OUT, or IN OUT bind parameters in TimesTen PL/SQL, such as from an OCI application. This enables arrays of data to be passed efficiently between an application and the database.

An associative array is a set of key-value pairs. In TimesTen, for associative array binding (but not for use of associative arrays only within PL/SQL), the keys, or indexes, must be integers—BINARY_INTEGER or PLS_INTEGER. The values must be simple scalar values of the same data type. For example, there could be an array of department managers indexed by department numbers. Indexes are stored in sort order, not creation order.

You can declare an associative array type and then an associative array from PL/SQL as in the following example (note the INDEX BY): 

declare
   TYPE VARCHARARRTYP IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
   x VARCHARARRTYP;
   ...

For Pro*C/C++, see Associative Array Bindings in TimesTen Pro*C/C++.

For related information, see Using Associative Arrays From Applications in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

Note:

Be aware of the following restrictions in TimesTen:

  • The following types are not supported in binding associative arrays: LOBs, REF CURSORs, TIMESTAMP, ROWID.

  • Associative array binding is not allowed in passthrough statements.

  • General bulk binding of arrays is not supported in TimesTen OCI. Varrays and nested tables are not supported as bind parameters.

TimesTen supports associative array binds in OCI by supporting the maxarr_len and *curelep parameters of the OCIBindByName() and OCIBindByPos() functions. These parameters are used to indicate that the binding is for an associative array.

The complete calling sequences for those functions are as follows:

sword OCIBindByName ( OCIStmt *stmtp,
                      OCIBind **bindpp,
                      OCIError *errhp,
                      const OraText *placeholder,
                      sb4 placeh_len,
                      void *valuep,
                      sb4 value_sz,
                      ub2 dty,
                      void *indp,
                      ub2 *alenp,
                      ub2 *rcodep,
                      ub4 maxarr_len,
                      ub4 *curelep,
                      ub4 mode );

sword OCIBindByPos ( OCIStmt *stmtp,
                     OCIBind **bindpp,
                     OCIError *errhp,
                     ub4 position,
                     void *valuep,
                     sb4 value_sz,
                     ub2 dty,
                     void *indp,
                     ub2 *alenp,
                     ub2 *rcodep,
                     ub4 maxarr_len,
                     ub4 *curelep,
                     ub4 mode );

The maxarr_len and *curelep parameters are used as follows when you bind an associative array. (They should be set to 0 if you are not binding an associative array.)

  • maxarr_len: This is an input parameter indicating the maximum array length. This is the maximum number of elements that the associative array can accommodate.

  • *curelep: This is an input/output parameter indicating the current array length. It is a pointer to the actual number of elements in the associative array before and after statement execution.

See OCIBindByName() and OCIBindByPos() in Oracle Call Interface Programmer's Guide.

In the following example, an OCI application binds an integer array and a character array to corresponding OUT associative arrays in a PL/SQL procedure.

Assume this SQL setup:

DROP TABLE FOO;
 
CREATE TABLE FOO (CNUM INTEGER,
                  CVC2 VARCHAR2(20));
 
INSERT INTO FOO VALUES ( null,
     'VARCHAR  1');
INSERT INTO FOO VALUES (-102,
     null);
INSERT INTO FOO VALUES ( 103,
     'VARCHAR  3');
INSERT INTO FOO VALUES (-104,
     'VARCHAR  4');
INSERT INTO FOO VALUES ( 105,
     'VARCHAR  5');
INSERT INTO FOO VALUES ( 106,
     'VARCHAR  6');
INSERT INTO FOO VALUES ( 107,
     'VARCHAR  7');
INSERT INTO FOO VALUES ( 108,
     'VARCHAR  8');
 
COMMIT;

Assume this PL/SQL package definition. This has the INTEGER associative array type NUMARRTYP and the VARCHAR2 associative array type VCHARRTYP, used for output associative arrays c1 and c2, respectively, in the definition of procedure P1. 

CREATE OR REPLACE PACKAGE PKG1 AS
  TYPE NUMARRTYP IS TABLE OF INTEGER INDEX BY BINARY_INTEGER;
  TYPE VCHARRTYP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP);
 
END PKG1;
/
 
CREATE OR REPLACE PACKAGE BODY PKG1 AS
 
  CURSOR CUR1 IS SELECT CNUM, CVC2 FROM FOO;
 
  PROCEDURE P1(c1 OUT NUMARRTYP,c2 OUT VCHARRTYP) IS
  BEGIN
    IF NOT CUR1%ISOPEN  THEN
      OPEN CUR1;
    END IF;
    FOR i IN 1..8 LOOP
      FETCH CUR1 INTO c1(i), c2(i);
      IF CUR1%NOTFOUND THEN
        CLOSE CUR1;
        EXIT;
      END IF;
    END LOOP;
  END P1;
 
END PKG1;

The following OCI program calls PKG1.P1, binds arrays to the P1 output associative arrays, and prints the contents of those associative arrays. Note in particular the OCIBindByName() function calls to do the binding. 

static OCIEnv *envhp;
static OCIServer *srvhp;
static OCISvcCtx *svchp;
static OCIError *errhp;
static OCISession *authp;
static OCIStmt *stmthp;
static OCIBind *bndhp[MAXCOLS];
static OCIBind *dfnhp[MAXCOLS];
 
STATICF VOID outbnd_1()
{
  int   i;
  int   num[MAXROWS];
  char*   vch[MAXROWS][20];
 
  unsigned int    numcnt   = 5;
  unsigned int    vchcnt = 5;
 
  unsigned short    alen_num[MAXROWS];
  unsigned short    alen_vch[MAXROWS];
  unsigned short    rc_num[MAXROWS];
  unsigned short    rc_vch[MAXROWS];
 
  short    indp_num[MAXROWS];
  short    indp_vch[MAXROWS];
 
/* Assume the process is connected and srvhp, svchp, errhp, authp, and stmthp 
   are all allocated/initialized/etc. */
 
  char  *sqlstmt = (char *)"BEGIN PKG1.P1(:c1, :c2); END; ";
 
  for (i = 0; i < MAXROWS; i++)
  {
    alen_num[i] = 0;
    alen_vch[i] = 0;
    rc_num[i] = 0;
    rc_vch[i] = 0;
    indp_num[i] = 0;
    indp_vch[i] = 0;
  }
 
  DISCARD printf("Running outbnd_1.\n");
  DISCARD printf("\n----> %s\n", sqlstmt);
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
           (unsigned int)strlen((char *)sqlstmt),
           (unsigned int) OCI_NTV_SYNTAX, (unsigned int) OCI_DEFAULT));
 
  bndhp[0] = 0;
  bndhp[1] = 0;
 
  checkerr(errhp, OCIBindByName(stmthp, &bndhp[0], errhp,
                  (char *) ":c1", (sb4) strlen((char *) ":c1"),
                  (dvoid *) &num[0], (sb4) sizeof(num[0]), SQLT_INT,
                  (dvoid *) &indp_num[0], (unsigned short *) &alen_num[0],
                  (unsigned short *) &rc_num[0],
                  (unsigned int) MAXROWS, (unsigned int *) &numcnt, 
                  (unsigned int) OCI_DEFAULT));
 
  checkerr(errhp, OCIBindByName(stmthp, &bndhp[1], errhp,
                  (char *) ":c2", (sb4) strlen((char *) ":c2"),
                  (dvoid *) vch[0], (sb4) sizeof(vch[0]), SQLT_CHR,
                  (dvoid *) &indp_vch[0], (unsigned short *) &alen_vch[0],
                  (unsigned short *) &rc_vch[0],
                  (unsigned int) MAXROWS, (unsigned int *) &vchcnt, 
                  (unsigned int) OCI_DEFAULT));
 
  DISCARD printf("\nTo execute the PL/SQL statement.\n");
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (unsigned int) 1, 
                  (unsigned int) 0, (const OCISnapshot*) 0,
                  (OCISnapshot*) 0,  (unsigned int) OCI_DEFAULT));
 
  DISCARD printf("\nHere are the results:\n\n");
 
  DISCARD printf("Column 1, INTEGER: \n");
  for (i = 0; i < numcnt; i++)
  {
    if (indp_num[i] == -1)
      DISCARD printf("-NULL- ");
    else
      DISCARD printf("%5d, ", num[i]);
    DISCARD printf("ind = %d,  len = %d, rc = %d\n",
                             indp_num[i], alen_num[i], rc_num[i]);
  }
 
  DISCARD printf("\nColumn 2, VARCHAR2(20): \n");
  for (i = 0; i < vchcnt; i++)
  {
    if (indp_vch[i] == -1)
      DISCARD printf("-NULL-      ");
    else
      DISCARD printf("%.*s, ", alen_vch[i], vch[i]);
    DISCARD printf("ind = %d,  len = %d, rc = %d\n",
                             indp_vch[i], alen_vch[i], rc_vch[i]);
  }
 
  DISCARD printf("\nDone\n");
  return;
}

Note:

The alen_* arrays are arrays of lengths; the rc_* arrays are arrays of return codes; the indp_* arrays are arrays of indicators.

Using Cache Operations With TimesTen OCI

This section discusses TimesTen OCI features related to using cache operations in TimesTen Classic:

Specifying the Oracle Database Password in OCI for Cache

To use cache, there must be a cache administration user in the TimesTen database with the same name as an Oracle Database cache administration user who can select from and update the cached Oracle Database tables. This Oracle Database cache administration user could alternatively be a schema user. The password of the TimesTen cache administration user can be different from the password of the Oracle Database cache administration user. See Setting Up a Caching Infrastructure in Oracle TimesTen In-Memory Database Cache Guide.

For use of OCI with cache operations, TimesTen enables you to pass the Oracle Database cache administration user's password through OCI by appending it to the password field in an OCILogon() or OCILogon2() call when you log in to TimesTen. Use the attribute OraclePWD in the connect string, such as in the following example: 

text *cacheadmin = (text *)"cacheadmin1";
text *cachepwds = (text *)"ttpassword;OraclePWD=oraclepassword";
text *ttdbname = (text *)"tt_tnsname";
....
OCILogon2(envhp, errhp, &svchp,
       (text *)cacheadmin, (ub4)strlen(cacheadmin),
       (text *)cachepwds, (ub4)strlen(cachepwds),
       (text *)ttdbname, (ub4)strlen(ttdbname), OCI_DEFAULT));

You must always specify OraclePWD, even if the Oracle Database cache administration user's password is the same as the TimesTen cache administration user's password.

Note the following for the example:

  • The name of the TimesTen cache administration user, as well as the name of the Oracle Database cache administration user who can access the cached Oracle Database tables, is cacheadmin1.

  • The password of the TimesTen cache administration user is ttpassword.

  • The password of the Oracle Database cache administration user is oraclepassword.

  • The TNS name of the TimesTen database being connected to is tt_tnsname.

The Oracle database is specified through the TimesTen OracleNetServiceName general connection attribute in the sys.odbc.ini or user odbc.ini file.

Alternatively, instead of using a TNS name, you could use easy connect syntax or the TWO_TASK or LOCAL environment variable, as discussed in preceding sections.

Determining the Number of Cache Instances Affected by an Action

In TimesTen OCI, following the execution of a FLUSH CACHE GROUP, LOAD CACHE GROUP, REFRESH CACHE GROUP, or UNLOAD CACHE GROUP statement, the OCI function OCIAttrGet() with the OCI_ATTR_ROW_COUNT argument returns the number of cache instances that were flushed, loaded, refreshed, or unloaded.

See Determining the Number of Cache Instances Affected by an Operation in the Oracle TimesTen In-Memory Database Cache Guide.

LOBs in TimesTen OCI

TimesTen Classic supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs). This section focuses on LOB locators, temporary LOBs, and OCI LOB APIs and features.

The following topics are covered here for OCI:

See the following for additional information:

  • Large Objects (LOBs), which is ODBC-oriented but also provides general overview of LOBs, differences between TimesTen and Oracle Database LOBs, and LOB programming interfaces

  • LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen

  • LOB and BFILE Operations in Oracle Call Interface Programmer's Guide for complete information about LOBs and how to use them in OCI, keeping in mind that TimesTen does not support BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs

Note:

The LOB piecewise data interface is not applicable to OCI applications. (You can, however, manipulate LOB data in pieces through features of the LOB locator interface.)

LOB Locators in OCI

OCI provides the LOB locator interface, where a LOB consists of a LOB locator and a LOB value. The locator acts as a handle to the value. When an application selects a LOB from the database, it receives a locator. When it updates the LOB, it does so through the locator. And when it passes a LOB as a parameter, it is passing the locator, not the actual value.

See About Using the LOB Locator Interface in OCI. (Note that in OCI it is also possible to use the simple data interface, which does not involve a locator. See Using the LOB Simple Data Interface in OCI.)

To update a LOB, your transaction must have an exclusive lock on the row containing the LOB. You can accomplish this by selecting the LOB with a SELECT ... FOR UPDATE statement. This results in a writable locator. With a simple SELECT statement, the locator is read-only. Read-only and writable locators behave as follows:

  • A read-only locator is read consistent, meaning that throughout its lifetime, it sees only the contents of the LOB as of the time it was selected. Note that this would include any uncommitted updates made to the LOB within the same transaction before the LOB was selected.

  • A writable locator is updated with the latest data from the database each time a write is made through the locator. So each write is made to the most current data of the LOB, including updates that have been made through other locators.

The following example details behavior for two writable locators for the same LOB:

  1. The LOB column contains "XY".

  2. Select locator L1 for update.

  3. Select locator L2 for update.

  4. Write "Z" through L1 at offset 1.

  5. Read through locator L1. This would return "ZY".

  6. Read through locator L2. This would return "XY", because L2 remains read-consistent until it is used for a write.

  7. Write "W" through L2 at offset 2.

  8. Read through locator L2. This would return "ZW". Prior to the write in the preceding step, the locator was updated with the latest data ("ZY").

Temporary LOBs in OCI

A temporary LOB exists only within an application, and in TimesTen OCI has a lifetime no longer than the transaction in which it was created (as is the case with the lifetime of any LOB locator in TimesTen). You can think of a temporary LOB as a scratch area for LOB data.

An OCI application can instantiate a temporary LOB explicitly, for use within the application, through the appropriate API. (See About Using the LOB Locator Interface in OCI.) A temporary LOB may also be created implicitly by TimesTen. For example, if a SELECT statement selects a LOB concatenated with an additional string of characters, TimesTen implicitly creates a temporary LOB to contain the concatenated data and an OCI application would receive a locator for the temporary LOB.

Temporary LOBs are stored in the TimesTen temporary data region.

Differences Between TimesTen LOBs and Oracle Database LOBs in OCI

A key difference between the LOB implementation for TimesTen versus Oracle Database is that in TimesTen, LOB locators do not remain valid past the end of the transaction. All LOB locators are invalidated after a commit or rollback, whether explicit or implicit. This includes after any DDL statement.

Also see Differences Between TimesTen LOBs and Oracle Database LOBs.

Using the LOB Simple Data Interface in OCI

The simple data interface enables applications to access LOB data by binding and defining, as with other scalar data types. The application can use a LOB type that is compatible with the corresponding variable type.

Use OCIStmtPrepare() to prepare a statement. For binding parameters, use OCIBindByName() or OCIBindByPos(). For defining result columns, use OCIDefineByPos().

For example, an OCI application can bind a CLOB parameter by calling OCIBindByName() with a data type of SQLT_CHR. Use OCIStmtExecute() to execute the statement. For an NCLOB parameter, use data type SQLT_CHR and set the OCI csform attribute (OCI_ATTR_CHARSET_FORM) to SQLCS_NCHAR. For a BLOB parameter, you can use data type SQLT_BIN.

Use of the simple data interface through OCI is shown in the following examples.

Note:

The simple data interface, through OCIBindByName(), OCIBindByPos(), or OCIDefineByPos(), limits bind sizes to 64 KB.

For examples that follow, assume the table and variables shown here.

person(ssn number, resume clob)
 
OCIEnv *envhp;
OCIServer *srvhp;
OCISvcCtx *svchp;
OCIError *errhp;
OCISession *authp;
OCIStmt *stmthp;
 
/* Bind Handles */
OCIBind *bndp1 = (OCIBind *) NULL; 
OCIBind *bndp2 = (OCIBind *) NULL;
 
/* Define Handles */
OCIDefine *defnp1 = (OCIDefine *) NULL;
OCIDefine *defnp2 = (OCIDefine *) NULL;
 
#define DATA_SIZE 50
#define PIECE_SIZE 10
#define NPIECE (DATA_SIZE/PIECE_SIZE)
 
char col2[DATA_SIZE];
char col2Res[DATA_SIZE];
ub2 col2len = DATA_SIZE;
sb4 ssn = 123456;
...

text *ins_stmt = (text *)"INSERT INTO PERSON VALUES (:1, :2)";
text *sel_stmt = (text *)"SELECT * FROM PERSON_1 ORDER BY SSN";
...

The following example executes an INSERT statement using the simple data interface in OCI. 

for (i=0;i<DATA_SIZE;i++) 
  col2[i] = 'A';
 
/* prepare SQL insert statement */
OCIStmtPrepare (stmthp, errhp, ins_stmt, strlen(ins_stmt), OCI_NTV_SYNTAX,
    OCI_DEFAULT);
 
/* bind parameters 1 and 2 using OCI_DEFAULT (not data-at-exec) */
OCIBindByPos (stmthp, &bndp1, errhp, 1, (dvoid *) &ssn, sizeof(ssn), 
 SQLT_INT, 0, 0, 0, 0, 0, OCI_DEFAULT);
OCIBindByPos (stmthp, &bndp2, errhp, 2, (dvoid *) col2, col2len,
                           SQLT_CHR, 0, 0, 0, 0, 0, OCI_DEFAULT);
 
/* execute insert statement */
OCIStmtExecute (svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT);

This next example executes a SELECT statement using the simple data interface in OCI. It uses the SELECT statement through the variable sel_stmt defined above. 

/* prepare select statement */
OCIStmtPrepare (stmthp, errhp, sel_stmt, strlen(sel_stmt), OCI_NTV_SYNTAX,
    OCI_DEFAULT);
 
/* define result columns 1 and 2 using OCI_DEFAULT (not data-at-exec) */
OCIDefineByPos (stmthp, &defnp1, errhp, 1, (dvoid*) &ssn, sizeof(ssn), 
                              SQLT_INT, 0, 0, 0, OCI_DEFAULT);
OCIDefineByPos (stmthp, &defnp2, errhp, 2, (dvoid *) col2Res, sizeof(col2), 
                              SQLT_CHR, 0, &col2len, 0, OCI_DEFAULT);
 
/* execute select statement */
OCIStmtExecute (svchp, stmthp, errhp, (ub4)1, (ub4)0, (OCISnapshot *) NULL, 
                              (OCISnapshot *) NULL, OCI_DEFAULT));
 
/* col2Res should now have a DATA_SIZE sized string of 'A's. */

About Using the LOB Locator Interface in OCI

You can use the OCI LOB locator interface to work with either a LOB from the database or a temporary LOB, either piece-by-piece or in whole chunks.

In order to use the LOB locator interface, the application must have a valid LOB locator. For a temporary LOB, this may be obtained explicitly through an OCILobCreateTemporary() call, or implicitly through a SQL statement that results in creation of a temporary LOB (such as SELECT c1 || c2 FROM myclob). For a persistent LOB, use a SQL statement to obtain the LOB locator from the database. (There are examples later in this section.)

Bind types are SQLT_CLOB for CLOBs and SQLT_BLOB for BLOBs. For NCLOBs, use SQLT_CLOB and also set the OCI csform attribute (OCI_ATTR_CHARSET_FORM) to SQLCS_NCHAR.

Sections that follow discuss using LOB locators in various scenarios.

Refer to LOB Functions in Oracle Call Interface Programmer's Guide for detailed information and additional examples for OCI LOB functions, noting that TimesTen does not support features specifically intended for BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs.

Tip:

LOB manipulations through APIs that use LOB locators result in usage of TimesTen temporary space. Any significant number of such manipulations may necessitate a size increase for the TimesTen temporary data region. See TempSize in Oracle TimesTen In-Memory Database Reference.

Note:

  • If an invalid LOB locator is assigned to another LOB locator using OCILobLocatorAssign(), the target of the assignment is also freed and marked as invalid.

  • OCILobLocatorAssign() can be used on a temporary LOB, but OCILobAssign() cannot.

Creating a Temporary LOB in OCI

An OCI application can create a temporary LOB by using the OCILobCreateTemporary() function, which has an input/output parameter for the LOB locator, after first calling OCIDescriptorAlloc() to allocate the locator. When you are finished, use OCIDescriptorFree() to free the allocation for the locator and use OCILobFreeTemporary() to free the temporary LOB itself.

Tip:

In TimesTen, creation of a temporary LOB results in creation of a database transaction if one is not already in progress. To avoid error conditions, you must execute a commit or rollback to close the transaction.

In TimesTen, any duration supported by Oracle Database (OCI_DURATION_SESSION, OCI_DURATION_TRANSACTION, or OCI_DURATION_CALL) is permissible in the OCILobCreateTemporary() call; however, in TimesTen the lifetime of the temporary LOB itself is no longer than the lifetime of the transaction.

Note that the lifetime of a temporary LOB can be shorter than the lifetime of the transaction in the following scenarios:

  • If OCI_DURATION_CALL is specified

  • If the application calls OCILobFreeTemporary() on the locator before the end of the transaction

  • If the application calls OCIDurationBegin() to start a user-specified duration for the temporary LOB, then calls OCIDurationEnd() before the end of the transaction

Following are examples of some of the OCI LOB functions mentioned above. See Temporary LOB Support in Oracle Call Interface Programmer's Guide. 

if (OCIDescriptorAlloc((void*)envhp, (void **)&tblob,(ub4)OCI_DTYPE_LOB,
    (size_t)0, (void**)0))
{
   printf("failed in OCIDescriptor Alloc in select_and_createtemp \n");
   return OCI_ERROR;
}
 
...
 
if (OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT,
    OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_TRANSACTION))
{
   (void) printf("FAILED: OCILobCreateTemporary() \n");
   return OCI_ERROR;
}
 
...
 
if(OCILobFreeTemporary(svchp,errhp,tblob))
{
   printf ("FAILED: OCILobFreeTemporary() call \n");
   return OCI_ERROR;
}

Accessing the Locator of a Persistent LOB in OCI

An application typically accesses a LOB from the database by using a SQL statement to obtain or access a LOB locator, then passing the locator to an appropriate API function.

A LOB that has been created using the EMPTY_CLOB() or EMPTY_BLOB() SQL function has a valid locator, which an application can then use to insert data into the LOB by selecting it.

Assume the following table definition:

CREATE TABLE clobtable (x NUMBER, y DATE, z VARCHAR2(30), lobcol CLOB);

  1. Prepare an INSERT statement. For example: 

    INSERT INTO clobtable ( x, y, z, lobcol )
   VALUES ( 81, sysdate, 'giants', EMPTY_CLOB() )
   RETURNING lobcol INTO :a;

    Or, to initialize the LOB with some data:

    INSERT INTO clobtable ( x, y, z, lobcol )
   VALUES ( 81, sysdate, 'giants', 'The Giants finally won a World Series' )
   RETURNING lobcol INTO :a;

  2. Bind the LOB locator to :a as shown.

  3. Execute the statement. After execution, the locator refers to the newly created LOB.

Then the application can use the LOB locator interface to read or write LOB data through the locator.

Alternatively, an application can use a SELECT statement to access the locator of an existing LOB.

The example that follows uses this table:

person(ssn number, resume clob)

It selects the locator for the LOB column in the PERSON table. 

text *ins_stmt = (text *)"INSERT INTO PERSON VALUES (:1, :2)";
text *sel_stmt = (text *)"SELECT * FROM PERSON WHERE SSN = 123456";
text *ins_empty = (text *)"INSERT INTO PERSON VALUES ( 1, EMPTY_CLOB())";
 
OCILobLocator *lobp;
 
ub4   amtp = DATA_SIZE;
ub4   remainder = DATA_SIZE;
ub4    nbytes = PIECE_SIZE;
 
/* Allocate lob locator */
OCIDescriptorAlloc (envhp, &lobp, OCI_DTYPE_LOB, 0, 0);
 
/* Insert an empty locator */
OCIStmtPrepare (stmhp, errhp, ins_empty, strlen(ins_empty), OCI_NTV_SYNTAX, 
                               OCI_DEFAULT);
OCIStmtExecute (svchp, stmhp, errhp, 1, 0, 0, 0, OCI_DEFAULT);
 
/* Now select the locator */
 
OCIStmtPrepare (stmhp, errhp, sel_stmt, strlen(sel_stmt), OCI_NTV_SYNTAX,
                OCI_DEFAULT);
 
/* Call define for the lob column */
OCIDefineByPos (stmthp, &defnp2, errhp, 1, &lobp, 0 , SQLT_CLOB, 0, 0, 0,
                OCI_DEFAULT);
 
OCIStmtExecute (svchp, stmhp, errhp, 1, 0, 0, 0, OCI_DEFAULT);

Reading and Writing LOB Data Using the OCI LOB Locator Interface

An OCI application can use OCILobOpen() and OCILobClose() to open and close a LOB. If you do not explicitly open and close a LOB, it is opened implicitly before a read or write and closed implicitly at the end of the transaction.

An application can use OCILobRead() or OCILobRead2() to read LOB data, OCILobWrite() or OCILobWrite2() to write LOB data, OCILobWriteAppend() or OCILobWriteAppend2() to append LOB data, OCILobErase() or OCILobErase2() to erase LOB data, and various other OCI functions to perform a variety of other actions.

For example, consider a CLOB with the content "Hello World!" You can overwrite and append data by calling OCILobWrite() with an offset of 7 to write "I am a new string". This would result in CLOB content being updated to "Hello I am a new string". Or, to erase data from the original "Hello World!" CLOB, you can call OCILobErase() with an offset of 7 and an amount (number of characters) of 5, for example, to update the CLOB to "Hello      !" (six spaces).

All the OCI LOB locator interface functions are covered in detail in LOB Functions in Oracle Call Interface Programmer's Guide.

Note:

  • Oracle Database emphasizes use of the "2" versions of the OCI read and write functions for LOBs (the non-"2" versions were deprecated in the Oracle Database 11.2 release); however, currently in TimesTen there is no technical advantage in using OCILobRead2(), OCILobWrite2(), and OCILobWriteAppend2(), which are intended for LOBs larger than what TimesTen supports.

  • In using any of the LOB read or write functions, be aware that the callback function parameter must be set to NULL or 0, because TimesTen does not support callback functions for LOB manipulation.

  • Because TimesTen does not support binding arrays of LOBs, the OCILobArrayRead() and OCILobArrayWrite() functions are not supported.

The following example shows how to write LOB data using the OCI LOB function OCILobWrite() and how to read data using OCILobRead(). It uses the table and variables from the example in the preceding section. 

for (i=0;i<DATA_SIZE;i++) 
  col2[i] = 'A';
 
/*************** Writing to the LOB *****************/
 
amt = DATA_SIZE;
offset = 1;
 
/* Write contents of col2 buffer into the LOB in a single chunk via locator lobp */
OCILobWrite (svchp, errhp, lobp, &amt, offset, col2, DATA_SIZE, OCI_ONE_PIECE,
                        0, 0, 0, SQLCS_IMPLICIT);
 
/*************** Reading from the LOB *****************/
 
/* Get the length of the LOB */
OCILobGetLength (svchp, errhp, lobp, &len);
amt = len;
 
/* Read the LOB data in col2Res in a single chunk */
OCILobRead (svchp, errhp, lobp, &amt, offset, col2Res, DATA_SIZE, 0, 0, 0,  
                        SQLCS_IMPLICIT);

OCI Client-Side Buffering

OCI provides a facility for client-side buffering on a per-LOB basis. It is enabled for a LOB by a call to OCILobEnableBuffering() and disabled by a call to OCILobDisableBuffering().

Enabling buffering for a LOB locator creates a 512 KB write buffer. This size is not configurable. Data written by the application through the LOB locator is buffered. When possible, the client library satisfies LOB read requests from the buffer as well. An application can flush the buffer by a call to OCILobFlushBuffer(). Note that buffers are not flushed automatically when they become full, and an attempt to write to the LOB through the locator when the buffer is full results in an error.

The following restrictions apply when you use client-side buffering:

  • Buffering is incompatible with the following functions: OCILobAppend(), OCILobCopy(), OCILobCopy2(), OCILobErase(), OCILobGetLength(), OCILobTrim(), OCILobWriteAppend(), and OCILobWriteAppend2().

  • An application can use OCILobWrite() or OCILobWrite2() only to append to the end of a LOB.

  • LOB data becomes visible to SQL and PL/SQL (server-side) operations only after the application has flushed the buffer.

  • When a LOB is selected while there are unflushed client-side writes in its buffer, the unflushed data is not included in the select.

LOB Prefetching in OCI

To reduce round trips to the server in client/server connections, LOB data can be prefetched from the database and cached on the client side during fetch operations. LOB prefetching in OCI has the same functionality in TimesTen as in Oracle Database.

Configure LOB prefetching through the following OCI attributes. Note that size refers to bytes for BLOBs and to characters for CLOBs or NCLOBs.

  • OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE: Use this to enable prefetching and specify the default prefetch size. A value of 0 (default) disables prefetching.

  • OCI_ATTR_LOBPREFETCH_SIZE: Set this attribute for a column define handle to specify the prefetch size for the particular LOB column.

  • OCI_ATTR_LOBPREFETCH_LENGTH: This attribute can be set TRUE or FALSE (default) to prefetch LOB metadata such as LOB length and chunk size.

The OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE and OCI_ATTR_LOBPREFETCH_LENGTH settings are independent of each other. You can use LOB data prefetching independently of LOB metadata prefetching.

Refer to Prefetching of LOB Data, Length, and Chunk Size in Oracle Call Interface Programmer's Guide.

Note:

The above attribute settings are ignored for direct connections to the database.

Passthrough LOBs in OCI

Passthrough LOBs (LOBs in Oracle Database accessed through TimesTen) are exposed as TimesTen LOBs and are supported by TimesTen in much the same way that any TimesTen LOB is supported.

Note the following:

  • You cannot use OCILobCreateTemporary() to create a passthrough LOB.

  • In addition to copying from one TimesTen LOB to another TimesTen LOB—such as through OCILobCopy(), OCILobCopy2(), or OCILobAppend()—you can copy from a TimesTen LOB to a passthrough LOB, from a passthrough LOB to a TimesTen LOB, or from one passthrough LOB to another passthrough LOB. Any of these copies the LOB value to the target destination. For example, copying a passthrough LOB to a TimesTen LOB copies the LOB value into the TimesTen database.

    An attempt to copy a passthrough LOB to a TimesTen LOB when the passthrough LOB is larger than the TimesTen LOB size limit results in an error.

  • TimesTen LOB size limitations do not apply to storage of LOBs in the Oracle database through passthrough. If a passthrough LOB is copied to a TimesTen LOB, the size limit applies to the copy.

  • As with TimesTen local LOBs, a locator for a passthrough LOB does not remain valid past the end of the transaction.

The examples that follow highlight key functionality in copying between TimesTen LOBs and passthrough LOBs on Oracle Database. After the table and data setup, the first example uses OCILobAppend() to copy LOB data from Oracle Database to TimesTen and the second example uses OCILobCopy() to copy LOB data from TimesTen to Oracle Database. (Either call could be used in either case.) Then, for contrast, the third example uses an UPDATE statement to copy LOB data from Oracle Database to TimesTen and the fourth example uses an INSERT statement to copy LOB data from TimesTen to Oracle Database. 

   /* Table and data setup */
  call ttoptsetflag(''passthrough'', 3)';
  DROP TABLE oratab';
  CREATE TABLE oratab (i INT, c CLOB)';
  INSERT INTO oratab VALUES (1, ''Copy from Oracle to TimesTen'')';
  INSERT INTO oratab VALUES (2, EMPTY_CLOB())';
  COMMIT;
 
  call ttoptsetflag(''passthrough'', 0)';
  DROP TABLE tttab';
  CREATE TABLE tttab (i INT, c CLOB)';
  INSERT INTO tttab VALUES (1, ''Copy from TimesTen to Oracle'')';
  INSERT INTO tttab VALUES (2, EMPTY_CLOB())';
  INSERT INTO tttab VALUES (3, NULL)';
  COMMIT;
  /* Table and data setup end */
 
  /*
   * Below are four OCI pseudocode examples, for copying LOBs between
   * TimesTen and Oracle using OCI API and INSERT/UPDATE statements.
   */
 
  /* Init OCI Env */
 
  /* Set the passthrough level to 1 */
  OCIStmtPrepare (..., "call ttoptsetflag(''passthrough'', 1)'", ...);
  OCIStmtExecute (...);
 
  /*
   * 1. Copy a passthrough LOB on Oracle to a TimesTen LOB */
 
  /* Select a passthrough locator on Oracle */
  OCIStmtPrepare (..., "SELECT c FROM oratab WHERE i = 1", ...);
  OCIDefineByPos (..., (dvoid *)&ora_loc_1, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  /* Select a locator on TimesTen for update */
  OCIStmtPrepare (..., "SELECT c FROM tttab WHERE i = 2 FOR UPDATE", ...);
  OCIDefineByPos (..., (dvoid *)&tt_loc_2, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  /* Copy a passthrough LOB on Oracle to a TimesTen LOB */
  OCILobAppend(..., tt_loc_2, ora_loc_1);
 
  /*
   * 2. Copy a TimesTen LOB to a passthrough LOB on Oracle */
 
  /* Select a passthrough locator on Oracle for update */
  OCIStmtPrepare (..., "SELECT c FROM oratab WHERE i = 2 FOR UPDATE", ...);
  OCIDefineByPos (..., (dvoid *)&ora_loc_2, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  /* Select a locator on TimesTen */
  OCIStmtPrepare (..., "SELECT c FROM tttab WHERE i = 1", ...);
  OCIDefineByPos (..., (dvoid *)&tt_loc_1, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  /* Copy a passthrough LOB on Oracle to a TimesTen LOB */
  OCILobCopy(..., ora_loc_2, tt_loc_1, 28, 1, 1);
 
  /*
   * 3. UPDATE a TimesTen LOB with a passthrough LOB on Oracle */
 
  /* A passthrough LOB, (selected above in case 1) is bound to an UPDATE statement
   * on TimesTen table */
  OCIStmtPrepare (..., "UPDATE tttab SET c = :1 WHERE i = 3", ...);
  OCIBindByPos (..., (dvoid *)&ora_loc_1, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  /*
   * 4. INSERT a passthrough table on Oracle with a TimesTen LOB */
 
  /* A TimesTen LOB, (selected above in case 2) is bound to an INSERT statement
   * on a passthough table on Oracle */
  OCIStmtPrepare (..., "INSERT INTO oratab VALUES (3, :1)", ...);
  OCIBindByPos (..., (dvoid *)&tt_loc_1, 0 , SQLT_CLOB, ...);
  OCIStmtExecute (...);
 
  OCITransCommit (...);
 
  /* Cleanup OCI Env */

Configuring the Result Set Buffer Size in Client/Server Using OCI

For data returned from a SELECT statement in client/server, the buffer size for the data returned to the client is configurable to allow adjustments for better performance. (In earlier releases, the buffer size could not be changed.)

The buffer size can be set in terms of either rows of data or bytes of data. The lower limit takes precedence. It is suggested to use one limit and set the other to a value high enough to ensure that it is not reached first.

Use these OCI statement attributes:

  • OCI_ATTR_PREFETCH_ROWS: Buffer size in rows (default 8192)
  • OCI_ATTR_PREFETCH_MEMORY: Buffer size in bytes (default 2097152, or 2 MB)

You can set these attributes but not get them.

Here is an example:


/* Double the row limit. */
ub4 rowsvalue = 16384;
...
OCIAttrSet(stmthp, handle_type, (dvoid *)&rowsvalue,
           sizeof(ub4), OCI_ATTR_PREFETCH_ROWS, errhp);

Note:

  • These attributes correspond to TimesTen connection attributes TT_NetMsgMaxRows and TT_NetMsgMaxBytes, which you can set in a TimesTen connection string or DSN, to serve as initial values for any statements created on the connection.
  • The minimum value of each attribute is 1 and at least one row is always returned. Setting either to a value of 0 results in the default value being used. There are no maximum settings other than the maximum value of the datatype (32-bit unsigned integer).
  • If a client version that supports these attributes connects to a server version that does not, any settings are ignored.

Use of PL/SQL in OCI to Call a TimesTen Built-In Procedure

TimesTen built-in procedures that return result sets are not supported directly through OCI.

This example shows how to use PL/SQL for this purpose.

plsql_resultset_example(OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp) 
{ 
  OCIStmt   *stmhp; 
  OCIBind   *bindp; 
 
  sb4        passThruValue = -1; 
  char       v_name[255]; 
  text      *stmt_text; 
  
  /* prepare the plsql statement */ 
  stmt_text = (text *) 
    "declare v_name varchar2(255); " 
    "begin execute immediate " 
       "'call ttOptGetFlag(''passthrough'')' into v_name, :rc1; " 
    "end;"; 
  OCIStmtPrepare2(svchp, &stmhp, errhp, (text *)stmt_text, 
                  (ub4)strlen((char *)stmt_text), 
                  (text *)0, (ub4)0, 
                  (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT); 
 
  /* bind parameter 1 (:v_name) to varchar2 out-parameter */ 
  OCIBindByPos(stmhp, &bindp, errhp, 1, 
               (dvoid*)&v_name, sizeof(v_name), SQLT_CHR, 
               (dvoid*)0, (ub2*)0, (ub2*)0, (ub4)0, (ub4*)0, 
               OCI_DEFAULT); 
 
  /* execute the plsql statement */ 
  OCIStmtExecute(svchp, stmhp, errhp, (ub4)1, (ub4)0, 
                 (OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT); 
 
  /* convert the passthrough string value to an integer */ 
  passThruValue = (sb4)atoi((const char *)v_name); 
  printf("Value of the passthrough flag is %d\n", passThruValue); 
 
  /* drop the statement handle */ 
  OCIStmtRelease(stmhp, errhp, (text *)0, (ub4)0, (ub4)OCI_DEFAULT); 
}