3 TimesTen Support for OCI

Character sets

To specify a character set for the connection, OCI programs can set the NLS_LANG environment variable or call OCIEnvNlsCreate(). The setting in the sys.odbc.ini or user odbc.ini file is used by default if not overridden by NLS_LANG or OCIEnvNlsCreate(). Setting the character set explicitly is recommended. The default is typically AMERICAN_AMERICA.US7ASCII.

Note that because TimesTen OCI does not support language or locale (territory) settings, the language and territory components of NLS_LANG, such as AMERICAN_AMERICA above, are ignored. Even when not specifying the language and locale, however, you must still have the period in front of the character set when setting NLS_LANG. For example, either of the following would work, although AMERICAN_AMERICA is ignored:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Or:

NLS_LANG=.WE8ISO8859P1

Additional globalization features

TimesTen OCI also supports the following additional globalization features. These can be set as environment variables, TimesTen general connection attributes, or TimesTen ODBC connection options. For the connection options, the names here are prepended by "TT_". An environment variable setting takes precedence over a corresponding connection attribute or connection option setting. A connection option setting takes precedence over a corresponding connection attribute setting.

• NLS_LENGTH_SEMANTICS: By default, the lengths of character data types CHAR and VARCHAR2 are specified in bytes, not characters. For single-byte character encoding this works well. For multibyte character encoding, you can use NLS_LENGTH_SEMANTICS to create CHAR and VARCHAR2 columns using character-length semantics instead. Supported settings are BYTE (default) and CHAR. (NCHAR and NVARCHAR2 columns are always character-based. Existing columns are not affected.)

• NLS_SORT: This specifies the type of sort for character data. It overrides the default value from NLS_LANGUAGE. Valid values are BINARY or any linguistic sort name supported by TimesTen. For example, to specify the German linguistic sort sequence, set NLS_SORT=German.

• NLS_NCHAR_CONV_EXCP: This determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR or NVARCHAR data and CHAR or VARCHAR2 data. Valid settings are TRUE and FALSE. The default value is FALSE, resulting in no error being reported.

Refer to "Globalization Support" in Oracle TimesTen In-Memory Database Operations Guide and "Setting Up a Globalization Support Environment" in Oracle Database Globalization Support Guide for additional information on these environment variables and related features. See "Option support for SQLSetConnectOption and SQLGetConnectOption" for information about TimesTen connection option support.

Oracle Database features not supported

TimesTen does not support OCI calls that are related to functionality that does not exist in TimesTen or IMDB Cache. For example, TimesTen and IMDB Cache do not support these Oracle Database features:

• Advanced Queuing

• Any Data

• Object support

• Collections

• Cartridge Services

• Direct path loading

• Date/time intervals

• Iterators

• BFILEs

• Cryptographic Toolkit

• XML DB support

• Spatial Services

• Event handling

• Session switching

• Scrollable cursors

Additional TimesTen OCI restrictions

TimesTen OCI has the following restrictions:

• The TypeMode data store attribute must be set to 0, which corresponds to Oracle Database behavior.

• The DuplicateBindMode general connection attribute must be set to 0, which corresponds to Oracle Database behavior.

• The DDLCommitBehavior general connection attribute must be set to 0, which corresponds to Oracle Database behavior.

• Asynchronous calls are not supported.

• Connection pooling and session pooling are not supported.

• Describing objects with OCIDescribeAny() is supported only by name. Describing PL/SQL objects is not supported. (Also see the entry for this function under "Supported OCI calls".)

• TimesTen Client/Server automatic client failover is not supported.

• The TNSPING utility does not recognize connections to TimesTen.

• Retrieving implicit ROWID values from INSERT, UPDATE, and DELETE statements is not supported. (This is supported for SELECT FOR UPDATE statements, however.)

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

• Only a single REF CURSOR can be returned from a PL/SQL block, procedure call, or function call.

• Binding and defining of structures through OCIBindArrayOfStruct() and OCIDefineArrayOfStruct() is supported for SQL statements but not for PL/SQL. (Also see the entries for these functions under "Supported OCI calls".)

• Oracle Database utilities such as SQL*Plus and SQL*Loader are not supported. (In TimesTen, you can use ttIsql instead of SQL*Plus and ttBulkCp instead of SQL*Loader. See "Utilities" in Oracle TimesTen In-Memory Database Reference.)

• Array binding, the ability to bind arrays into PL/SQL statements, is supported for associative arrays (index-by tables or PL/SQL tables) but is not supported for varrays (variable size arrays) or nested tables. (See "Associative array bindings in TimesTen OCI".)

Additional TimesTen OCI differences

Be aware of the following:

• Both TimesTen and Oracle Database support XA, but TimesTen does not support XA through OCI.

• With OCI, TimesTen automatically disables autocommit for DML statements. Transactions should be explicitly committed or rolled back when finished.

• There are differences in the usage of hexadecimal literals in TimesTen. See the description of HexadecimalLiteral in "Constants" in Oracle TimesTen In-Memory Database SQL Reference.

Using the tnsnames naming method to connect

TimesTen supports tnsnames syntax. You can use a TimesTen tnsnames.ora entry the same way you would use an Oracle tnsnames.ora entry.

The syntax of a TimesTen entry in tnsnames.ora is as follows:

tns_entry = (DESCRIPTION =
              (CONNECT_DATA =
                (SERVICE_NAME = dsn)
                (SERVER = timesten_direct | timesten_client)))

Where tns_entry is the arbitrary TNS name you assign to the entry. You can use this as the dbname argument in OCILogon(), OCILogon2(), and OCIServerAttach() calls.

DESCRIPTION and CONNECT_DATA are required as shown.

For SERVICE_NAME, dsn must be a TimesTen DSN that is configured in the sys.odbc.ini or user odbc.ini file that is visible to a user running your OCI application. On Windows, the DSN can be specified by using the ODBC Data Source Administrator. See "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide.

For SERVER, timesten_direct specifies a direct connection to TimesTen or timesten_client specifies a client/server connection. If you choose timesten_client, the DSN must be configured as a client/server database.

As always, the host and port of the TimesTen server are determined from entries in the sys.ttconnect.ini file, according to the DSN. See "Working with the TimesTen Client and Server" in Oracle TimesTen In-Memory Database Operations Guide.

Here is a sample tnsnames.ora entry for a direct connection:

my_tnsname = (DESCRIPTION = 
               (CONNECT_DATA = 
                 (SERVICE_NAME = my_dsn)
                 (SERVER = timesten_direct))) 

You can use the TNS name, my_tnsname, in either of the following ways:

• Specify "my_tnsname" for the dbname argument in your OCI logon call.

• Specify an empty string for dbname and set TWO_TASK or LOCAL to "my_tnsname".

For example:

OCILogon2(envhp, errhp, &svchp,
         (text *)"user1", (ub4)strlen("user1"),
         (text *)"pwd1", (ub4)strlen("pwd1"), 
         (text *)"my_tnsname", (ub4)strlen((char*)"my_tnsname"), OCI_DEFAULT)); 

Refer to "Connect, Authorize, and Initialize Functions" in Oracle Call Interface Programmer's Guide for details about OCI logon calling sequences.

Or on a UNIX system, for example, you can set TWO_TASK to "my_tnsname" and use an OCI logon call with an empty string for dbname:

OCILogon2(envhp, errhp, &svchp,
         (text *)"user1", (ub4)strlen("user1"),
         (text *)"pwd1", (ub4)strlen("pwd1"), 
         (text *)"", (ub4)0, OCI_DEFAULT)); 

Using an easy connect string to connect

TimesTen supports easy connect syntax, which enhances the Instant Client package by allowing connections to be made without configuring tnsnames.ora. An easy connect string has syntax similar to a URL, in the following format:

[//]host[:port]/service_name:server[/instance]

The initial double-slash is optional. A host name must be specified to satisfy easy connect syntax, but is otherwise ignored by TimesTen. The name "localhost" is typically used by convention. Any value specified for the port is also ignored. For client/server connections, the host and port of the TimesTen server are determined from entries in the sys.ttconnect.ini file, according to the TimesTen DSN.

Specify the DSN for service_name. Specify timesten_client or timesten_direct, as appropriate, for server.

TimesTen ignores the instance field and does not require that it be specified.

For example, the following easy connect string connects to a TimesTen server using the client/server libraries. Assume a DSN ttclient in the sys.odbc.ini file is resolved as a client/server data source and connects to the corresponding host and port specified in the sys.ttconnect.ini file:

"localhost/ttclient:timesten_client"

The following easy connect string is for a direct connection to TimesTen. Assume the DSN ttdirect is defined in sys.odbc.ini:

"localhost/ttdirect:timesten_direct"

You can use an easy connect string in either of the following ways:

• Specify it for the dbname argument in your OCI logon call.

• Specify an empty string for dbname and set TWO_TASK or LOCAL to the easy connect string, in quotes.

For example:

OCILogon2(envhp, errhp, &svchp,
         (text *)"user1", (ub4)strlen("user1"),
         (text *)"pwd1", (ub4)strlen("pwd1"),
         (text *)"localhost/ttclient:timesten_client",
         (ub4)strlen((char*)"localhost/ttclient:timesten_client"), OCI_DEFAULT));

Refer to "Connect, Authorize, and Initialize Functions" in Oracle Call Interface Programmer's Guide for details about OCI logon calling sequences.

Or on a UNIX system, for example, you can set TWO_TASK to "localhost/ttclient:timesten_client" and use an OCI logon call with an empty string for dbname:

OCILogon2(envhp, errhp, &svchp,
         (text *)"user1", (ub4)strlen("user1"),
         (text *)"pwd1", (ub4)strlen("pwd1"), 
         (text *)"", (ub4)0, OCI_DEFAULT)); 

Configuring whether to use tnsnames.ora or easy connect

If a sqlnet.ora file is present, it specifies the naming methods that will be tried and the order in which they will be tried. The Instant Client will look for a sqlnet.ora file at the TNS_ADMIN location, if applicable. If TNS_ADMIN has not been set but ORACLE_HOME has been (such as if you had a previous Instant Client installation), the default sqlnet.ora location is the Oracle Database default location as noted in "Parameters for the sqlnet.ora File" in Oracle Database Net Services Reference.

If sqlnet.ora is found and does not indicate a particular naming method, you cannot use that method. If sqlnet.ora is not found, you can use either method.

In TimesTen, sample copies of tnsnames.ora and sqlnet.ora are in the install_dir/network/admin/samples directory. Here is the sqlnet.ora file that TimesTen provides, which supports both tnsnames and easy connect ("EZCONNECT"):

# To use ezconnect syntax or tnsnames, the following entries must be
# included in the sqlnet.ora configuration.
#
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

With this file, TimesTen will first look for tnsnames syntax in your OCI logon calls. If it cannot find tnsnames syntax, it will look for easy connect syntax.

Connecting as an externally identified user in OCI

You can connect through OCI as an externally identified user (external user) by specifying the user name in brackets, such as "[myadmin]", and the password as an empty string, "".

In particular, this is useful in connecting as the instance administrator, which in TimesTen is always an external user.

Adapting an earlier example:

OCILogon2(envhp, errhp, &svchp,
         (text *)"[myadmin]", (ub4)strlen("[myadmin]"),
         (text *)"", (ub4)strlen(""), 
         (text *)"my_tnsname", (ub4)strlen((char*)"my_tnsname"), OCI_DEFAULT)); 

This functionality uses OCI proxy syntax. You can refer to the discussion of client access through a proxy in Oracle Call Interface Programmer's Guide.

Duplicate parameter bindings in TimesTen OCI

"Binding duplicate parameters in SQL statements" discusses the two supported modes for binding duplicate parameters in a SQL statement, either the Oracle mode or the traditional TimesTen mode. As in that section, consider the following query. Note that in TimesTen OCI, only the Oracle mode is supported.

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

In OCI, as in the Oracle mode in general, two occurrences of parameter a are considered to be separate parameters. However, OCI allows both occurrences of a to be bound with a single call to OCIBindByPos():

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

Alternatively, OCI also allows the two occurrences of a to be bound 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.

Associative array bindings 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 array binds from applications" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

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.

For additional information about these functions, see "OCIBindByName()" and "OCIBindByPos()" in Oracle Call Interface Programmer's Guide.

In Example 3-1, an OCI application binds an integer array and a character array to corresponding OUT associative arrays in a PL/SQL procedure.

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;

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;

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;
}

Specifying the Oracle Database password in OCI for IMDB Cache

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

For use of OCI with the IMDB Cache, TimesTen allows you to pass the Oracle Database 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 *cacheuser = (text *)"cacheuser1";
text *cachepwds = (text *)"ttpwd;OraclePWD=orclpwd";
text *ttdbname = (text *)"tt_tnsname";
....
OCILogon2(envhp, errhp, &svchp,
       (text *)cacheuser, (ub4)strlen(cacheuser),
       (text *)cachepwds, (ub4)strlen(cachepwds),
       (text *)ttdbname, (ub4)strlen(ttdbname), OCI_DEFAULT)); 

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

Note the following for the example:

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

• The password of the TimesTen cache user is ttpwd.

• The password of the Oracle Database user is orclpwd.

• 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 groups 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.

For related information, see "Determining the number of cache instances affected by an operation" in the Oracle In-Memory Database Cache User's Guide.

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 "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 prior to when 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 "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 will implicitly create 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 partition.

Differences between TimesTen LOBs and Oracle Database LOBs in OCI

A key difference between the TimesTen LOB implementation and the Oracle Database implementation 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 if TimesTen DDLCommitBehavior is set to 0 (the default), for Oracle Database behavior.

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.

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";
...

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);

/* 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. */

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 is obtained through the OCILobCreateTemporary() call, for explicitly created LOBs, or through a SQL statement that results in implicit 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.

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.

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;
}

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

person(ssn number, resume clob)

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);

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 will satisfy 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 will result 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 for more information and an example.

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, but 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 will result in an error.

• TimesTen LOB size limitations do not apply to storage of passthrough LOBs, but do apply to binding. Also, 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.

/* 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 */