3 TimesTen Support for OCI

TimesTen and TimesTen Cache support the Oracle Call Interface (OCI) for C or C++ programs.

This chapter provides an overview and TimesTen-specific information regarding OCI, especially emphasizing differences between using OCI with TimesTen versus with Oracle Database. For complete information about OCI, you can refer to Oracle Call Interface Programmer's Guide in the Oracle Database library.

Also note that Chapter 2, "Working with TimesTen Databases in ODBC", contains information that may be of general interest regarding TimesTen features.

The following topics are covered:

Overview of OCI

OCI is an API that provides functions you can use to access the database and control SQL execution. OCI supports the data types, calling conventions, syntax, and semantics of the C and C++ programming languages. You compile and link an OCI program much as you would any C or C++ program. There is no preprocessing or precompilation step.

The OCI library of database access and retrieval functions is in the form of a dynamic runtime library that can be linked into an application at runtime. The OCI library includes the following functional areas:

  • SQL access functions

  • Data type mapping and manipulation functions

The following are among the many useful features that OCI provides or supports:

  • Statement caching

  • Dynamic SQL

  • Facilities to treat transaction control, session control, and system control statements like DML statements

  • Description functionality to expose layers of server metadata

  • Ability to associate commit requests with statement executions to reduce round trips

  • Optimization of queries using transparent prefetch buffers to reduce round trips

  • Thread safety that eliminates the need for mutual exclusive locks on OCI handles

For general information about OCI, you can refer to Oracle Call Interface Programmer's Guide, included with the Oracle Database documentation set.

Overview of TimesTen OCI support

This chapter contains information specific to using OCI with TimesTen and TimesTen Cache. For supported features, TimesTen OCI syntax and usage is the same as that in Oracle Database.

This section covers the following topics:

OCI libraries and architecture

TimesTen OCI support enables you to run many existing OCI applications with TimesTen direct connections or client/server connections. It also enables you to use other features, such as Pro*C/C++ and ODP.NET, that use OCI as a database interface. (You can also call PL/SQL from OCI, Pro*C/C++, and ODP.NET applications.) Figure 3-1 shows where OCI support is positioned in the TimesTen architecture.

TimesTen provides Oracle Instant Client as the OCI client library. This is configured through the appropriate ttenv script, discussed in "Environment variables" in the Oracle TimesTen In-Memory Database Installation Guide.

Figure 3-1 OCI in the TimesTen architecture

Description of Figure 3-1 follows
Description of "Figure 3-1 OCI in the TimesTen architecture"

TimesTen 11g Release 2 (11.2.2) OCI is based on Oracle Database release 11.2.0.2 OCI and supports the contemporary OCI 8 style APIs. For example, the OCIStmtExecute() function is supported but not the older oexec() function. See "Obsolete OCI Routines" in Oracle Call Interface Programmer's Guide in the Oracle Database documentation.

Globalization support

This section discusses TimesTen OCI support for globalization.

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

Notes:

  • TimesTen character sets are compatible with Oracle Database.

  • An NLS_LANG environment setting overrides the TimesTen default character set.

  • On Windows, the NLS_LANG setting is searched for in the registry if it is not in the environment. If your OCI or Pro*C/C++ program has trouble connecting to TimesTen, verify that the NLS_LANG setting under HKEY_LOCAL_MACHINE\Software\ORACLE\, if that key exists, is valid and indicates a character set supported by TimesTen.

  • Refer to "Choosing a Locale with the NLS_LANG Environment Variable" in Oracle Database Globalization Support Guide for further information about NLS_LANG.

  • The TimesTen default character set is AMERICAN_AMERICA.US7ASCII. The TIMESTEN8 character set is not supported. Refer to "Supported character sets" in Oracle TimesTen In-Memory Database Reference.

  • Refer to "OCIEnvNlsCreate()" in Oracle Call Interface Programmer's Guide for information about that OCI call.

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_LANG. 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.

TimesTen restrictions and differences

This section discusses the following areas of restrictions and differences for OCI in TimesTen compared to in the Oracle Database:

Oracle Database features not supported

TimesTen does not support OCI calls that are related to functionality that does not exist in TimesTen or TimesTen Cache. For example, TimesTen and TimesTen 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. You can, however, use PL/SQL for this purpose, as shown in "Use of PL/SQL in OCI to call a TimesTen built-in procedure".

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

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

The ttSrcScan utility

If you have an existing OCI program and want to see whether it uses OCI features that TimesTen does not support, you can use the ttSrcScan command line utility to scan your program for unsupported functions, types, type codes, attributes, modes, and constants. This is a standalone utility that can be run without TimesTen or Oracle Database being installed and runs on any platform supported by TimesTen. It reads source code files as input and creates HTML and text files as output. If the utility finds unsupported items, then they are logged and alternatives are suggested. You can find the ttSrcScan executable in the quickstart/sample_util directory in your TimesTen installation.

Specify an input file or directory for the program to be scanned and an output directory for the ttSrcScan reports. Other options are available as well. See the README file in the sample_util directory for information.

Getting started with TimesTen OCI

This section discusses the following topics for getting started with a TimesTen OCI application:

Environment variables for TimesTen OCI

Environment variables for executing a TimesTen OCI application are described in Table 3-1. Settings apply to both direct connections and client/server connections except as noted.

After installation, you can modify environment variables as appropriate through the TimesTen install_dir/bin/ttenv script or quickstart/ttquickstartenv script applicable to your operating system. See "Environment variables" in the Oracle TimesTen In-Memory Database Installation Guide for information about ttenv.

You can also use the TimesTen OCI and Pro*C/C++ Makefiles provided with the Quick Start demos to implement appropriate environment settings. These are in the following locations.

quickstart/sample_code/oci/
quickstart/sample_code/proc/

Note:

To ensure proper generation of OCI programs to be run on TimesTen, do not set ORACLE_HOME for OCI compilations (or unset it if it was set previously).

Table 3-1 Environment variables for TimesTen OCI

Variable Required or optional Settings

LD_LIBRARY_PATH (UNIX)

PATH (Windows)

Required

Must be set so that the TimesTen Instant Client directory precedes the Oracle Database libraries in the path. The path is set properly if you use either of the following scripts under install_dir (the second path assuming the standard Quick Start location):

bin/ttenv
quickstart/ttquickstartenv

See "Environment variables" in the Oracle TimesTen In-Memory Database Installation Guide for information about ttenv.

TNS_ADMIN

Required if you use the tnsnames naming method

Specifies the directory where the tnsnames.ora file is located. This is also where TimesTen looks for a sqlnet.ora file.

TWO_TASK (UNIX)

LOCAL (Windows)

Optional

You can use this, whichever is appropriate for your platform, instead of specifying the dbname argument in your OCI logon call. The setting consists of a valid TNS name or easy connect string.

See "Connecting to a TimesTen database from OCI" for more information.

NLS_LANG

Optional

See "Character sets". Only the character set component is honored and it must indicate a character set supported by TimesTen. The language and territory values are ignored.

This environment variable overrides the TimesTen default character set.

NLS_SORT

Optional

See "Additional globalization features". The sort order must be a value supported by TimesTen.

This overrides the TimesTen NLS_SORT general connection attribute.

NLS_LENGTH_SEMANTICS

Optional

See "Additional globalization features".

This overrides the TimesTen NLS_LENGTH_SEMANTICS general connection attribute.

NLS_NCHAR_CONV_EXCP

Optional

See "Additional globalization features".

This overrides the TimesTen NLS_NCHAR_CONV_EXCP general connection attribute.


Note:

Refer to "NLS general connection attributes" in Oracle TimesTen In-Memory Database Reference for information about the NLS connection attributes mentioned in the table.

Compiling and linking OCI applications

No changes are required between Oracle Database and TimesTen for the steps to compile and link an OCI application.

OCI programs that use the Oracle Client 11.2.0.2 library do not have to be recompiled or relinked to be executed with TimesTen.

Connecting to a TimesTen database from OCI

TimesTen OCI uses the Oracle Instant Client to connect to the TimesTen database. You can connect to the database through either the tnsnames or the easy connect naming method, similarly to how you would connect to an Oracle database through those methods.

This section covers the following topics:

Refer to "Configuring Naming Methods" in Oracle Database Net Services Administrator's Guide for additional information about tnsnames, easy connect, and the tnsnames.ora file.

Notes:

  • Although the sqlnet mechanism is used for a TimesTen OCI connection, the connection goes through the TimesTen ODBC driver, not the Oracle Database sqlnet driver.

  • Privilege to connect to the database must be explicitly granted, through the CREATE SESSION privilege, to every user other than the instance administrator who wants to connect to TimesTen. Refer to "Access control for connections".

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 Database 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, as follows.

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 are tried and the order in which they are tried. The Instant Client looks 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 first looks for tnsnames syntax in your OCI logon calls. If it cannot find tnsnames syntax, it looks 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.

Externally identified users can be used for direct mode or for client/server connections to a database on the local host, but not for client/server connections to a database on a remote host.

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.

OCI error reporting

Errors under TimesTen OCI applications return Oracle Database error codes. TimesTen attempts to report the same error code as Oracle Database would under similar conditions. The error messages may come from either the TimesTen catalog or the Oracle Database catalog. Some error messages may indicate the accompanying TimesTen error code if appropriate.

Fatal errors are those that make the database inaccessible until after error recovery. When a fatal error occurs, all database connections are required to disconnect in order to avoid out-of-memory conditions. No further operations may complete. Shared memory from the old TimesTen instance is not freed until all active connections at the time of the error have disconnected.

Fatal errors in OCI are indicated by the Oracle Database error code ORA-03135 or ORA-00600. Error handling for these errors should be different from standard error handling. In particular, the application error-handling code should have a disconnect from the database.

Signal handling and diagnostic framework considerations

The OCI diagnostic framework installs signal handlers that may impact any signal handling that you use in your application. You can disable OCI signal handling by setting DIAG_SIGHANDLER_ENABLED=FALSE in the sqlnet.ora file. Refer to "Fault Diagnosability in OCI" in Oracle Call Interface Programmer's Guide for information.

OCI demo programs

TimesTen ships OCI demo programs. They are in the quickstart/sample_code/oci directory. The README file in the directory explains how to compile and run the demos.

Refer to the Quick Start welcome page at install_dir/quickstart.html for information.

Use of additional features with TimesTen 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 allow 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

This section discusses features relating to binding parameters into SQL or PL/SQL from an OCI application:

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.

Note:

OCI also allows parameters to be bound by name, rather than by position, using OCIBindByName().

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

Notes:

Note 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.

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

Note:

In TimesTen, the OCIBindDynamic() function and the OCI_DATA_AT_EXEC mode setting for OCIBindByName() and OCIBindByPos() are not supported. (In Oracle Database, OCIBindDynamic() can be used to register user-defined callback functions to provide or receive data in "at exec" mode to set up additional bind attributes at execution time.)

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.

Example 3-1 Binding to an associative array from OCI

Assume the following 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 the following 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.

TimesTen Cache with TimesTen OCI

This section discusses TimesTen OCI features related using the TimesTen Cache:

Specifying the Oracle Database password in OCI for TimesTen Cache

To use TimesTen 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 TimesTen Application-Tier Database Cache User's Guide for details.

For use of OCI with the TimesTen 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 TimesTen Application-Tier Database Cache User's Guide.

LOBs in TimesTen OCI

TimesTen supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

See "Working with LOBs". That section is ODBC-oriented but also provides some general overview of LOBs, differences between TimesTen and Oracle Database LOBs, and LOB programming interfaces.

This section focuses on LOB locators, temporary LOBs, and OCI LOB APIs and features.

See "LOB data types" in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen.

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

The following topics are covered here for OCI:

Note:

The LOB piecewise data interface is not applicable to OCI applications in TimesTen. (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 "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 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 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.

Note:

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

Example 3-2 Example table and variables

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

Example 3-3 Insert LOB data using simple data interface

This example executes an INSERT statement using the simple data interface in OCI. It uses the table and variables from the preceding Example 3-2, including the INSERT statement defined through the variable ins_stmt.

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

Example 3-4 Select LOB data using simple data interface

This example executes a SELECT statement using the simple data interface in OCI. It uses the table and variables from the earlier Example 3-2, including the SELECT statement defined through the variable sel_stmt.

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

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.

Important:

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.

Notes:

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

Create 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.

Important:

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. For details about the use of temporary LOBs and a complete example, 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;
}
Access 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.

Example 3-5 Select LOB locator using LOB locator interface

This example uses the following 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);
Read and write 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.

Notes:

  • Oracle Database emphasizes use of the "2" versions of the OCI read and write functions for LOBs (the non-"2" versions are deprecated as of 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.

Example 3-6 Write and read LOB data using LOB locator interface

This 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 preceding Example 3-5.

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

Note:

The above attribute settings are ignored for TimesTen direct connections.

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

Example 3-7 Copying between TimesTen LOBs and passthrough LOBs

The examples here 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 */

Use of PL/SQL in OCI to call a TimesTen built-in procedure

As noted earlier in this chapter, TimesTen built-in procedures that return result sets are not supported directly through OCI. You can, however, use PL/SQL for this purpose, as shown in Example 3-8.

Example 3-8 Using PL/SQL in OCI to call a TimesTen built-in procedure

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

TimesTen OCI support reference

This is a reference section for TimesTen support of OCI features, covering the following areas:

Supported OCI calls

Table 3-2 lists TimesTen support for OCI calls that are documented for Oracle Database release 11.2.0.2.

Some groups of calls are represented with an asterisk in the name. For example, the calls related to Advanced Queuing, which TimesTen does not support, have names that start with OCIAQ and are represented in the table as OCIAQ*(). OCI date functions, which TimesTen does support, are designated by OCIDate*().

Table 3-2 TimesTen OCI supported calls

OCI call Supported Notes

OCIAQ*()

No

TimesTen does not support Advanced Queuing.

OCIAnyData*()

No

TimesTen does not support Any Data.

OCIAppCtxClearAll()

Yes

 

OCIAppCtxSet()

Yes

 

OCIArrayDescriptorAlloc()

Yes

 

OCIArrayDescriptorFree()

Yes

 

OCIAttrGet()

Yes

TimesTen support includes special usage with cache groups. See "TimesTen Cache with TimesTen OCI".

OCIAttrSet()

Yes

 

OCIBinXml*()

No

TimesTen does not support XML DB.

OCIBindArrayOfStruct()

Yes

This is supported for SQL statements but not PL/SQL.

OCIBindByName()

Yes

The following are unsupported values for the mode parameter:

  • OCI_DATA_AT_EXEC

  • OCI_IOV

OCIBindByPos()

Yes

The following are unsupported values for the mode parameter:

  • OCI_DATA_AT_EXEC

  • OCI_IOV

OCIBindDynamic()

No

 

OCIBindObject()

No

TimesTen does not support user-defined objects.

OCIBreak()

No

 

OCICache*()

No

TimesTen does not support user-defined objects.

OCICharSetConversionIsReplacementUsed()

Yes

 

OCICharSetToUnicode()

Yes

 

OCIClientVersion()

Yes

 

OCIColl*()

No

TimesTen does not support collections.

OCIConnectionPoolCreate()

No

 

OCIConnectionPoolDestroy()

No

 

OCIContext*()

No

TimesTen does not support Data Cartridge.

OCIDBShutdown()

No

 

OCIDBStartup()

No

 

OCIDate*()

Yes

See Table 3-4 for information about descriptor support.

OCIDefineArrayOfStruct()

Yes

This is supported for SQL statements but not PL/SQL.

OCIDefineByPos()

Yes

The following are unsupported values for the mode parameter:

  • OCI_DATA_AT_EXEC

  • OCI_IOV

OCIDefineDynamic()

No

 

OCIDefineObject()

No

 

OCIDescribeAny()

Yes

PL/SQL objects are not supported.

Describing objects is supported only by name.

The following are unsupported values for the objptr_typ parameter:

  • OCI_OTYPE_REF

  • OCI_OTYPE_PTR

The following are unsupported values for the objtyp parameter:

  • OCI_PTYPE_PKG

  • OCI_PTYPE_FUNC

  • OCI_PTYPE_PROC

  • OCI_PTYPE_SYN

  • OCI_PTYPE_TYPE

When you use the setting OCI_PTYPE_DATABASE for the objtyp parameter, use the predetermined name $TT_DB_NAME$ as the database name for the *objptr parameter.

OCIDescriptorAlloc()

Yes

 

OCIDescriptorFree()

Yes

 

OCIDirPath*()

No

TimesTen does not support Direct Path Loading.

OCIDurationBegin()

Yes

Supported for LOBs. Regardless of the duration setting, the duration cannot exceed the lifetime of the transaction.

OCIDurationEnd()

Yes

Supported for LOBs. Regardless of the duration setting, the duration cannot exceed the lifetime of the transaction.

OCIDuration*()

No

TimesTen does not support Data Cartridge.

OCIEnvCreate()

Yes

The following are unsupported values for the mode parameter:

  • OCI_EVENTS

  • OCI_NEW_LENGTH_SEMANTICS

  • OCI_NCHAR_LITERAL_REPLACE_ON

  • OCI_NCHAR_LITERAL_REPLACE_OFF

  • OCI_NO_MUTEX (Instead use OCI_ENV_NO_MUTEX.)

OCIEnvInit()

Yes

The following are unsupported values for the mode parameter:

  • OCI_NO_MUTEX

  • OCI_ENV_NO_MUTEX

Note: Use OCIEnvCreate() instead of OCIEnvInit(). OCIEnvInit() is supported for backward compatibility.

OCIEnvNlsCreate()

Yes

The following are unsupported values for the mode parameter:

  • OCI_EVENTS

  • OCI_NCHAR_LITERAL_REPLACE_ON

  • OCI_NCHAR_LITERAL_REPLACE_OFF

  • OCI_NO_MUTEX (Instead use OCI_ENV_NO_MUTEX.)

OCIErrorGet()

Yes

 

OCIExtProc*()

No

TimesTen does not support Data Cartridge.

OCIExtract*()

No

TimesTen does not support Data Cartridge.

OCIFile*()

No

TimesTen does not support Data Cartridge.

OCIFormatInit()

No

TimesTen does not support Data Cartridge.

OCIFormatString()

No

TimesTen does not support Data Cartridge.

OCIFormatTerm()

No

TimesTen does not support Data Cartridge.

OCIHandleAlloc()

Yes

 

OCIHandleFree()

Yes

 

OCIInitialize()

Yes

The following are unsupported values for the mode parameter:

  • OCI_NO_MUTEX

  • OCI_ENV_NO_MUTEX

Note: Use OCIEnvCreate() instead of OCIInitialize(). OCIInitialize() is supported for backward compatibility.

OCIInterval*()

Yes

See Table 3-4 for information about descriptor support.

OCIIter*()

No

TimesTen does not support collections.

OCILdaToSvcCtx()

No

 

OCILob*()

Yes

TimesTen supports OCILob*() functions other than the following:

  • Functions specifically intended for array reads and writes

  • Functions specifically intended for BFILEs

  • Functions specifically intended for SecureFiles

Notes:

OCILogoff()

Yes

 

OCILogon()

Yes

 

OCILogon2()

Yes

OCI_DEFAULT is the only supported value for the mode parameter.

OCIMemory*()

No

TimesTen does not support Data Cartridge.

OCIMessage*()

No

TimesTen does not support Data Cartridge.

OCIMultiByte*()

Yes

 

OCINls*()

Yes

 

OCINumber*()

Yes

 

OCIObject*()

No

TimesTen does not support user-defined objects.

OCIParamGet()

Yes

 

OCIParamSet()

Yes

 

OCIPasswordChange()

No

 

OCIPing()

Yes

 

OCIRaw*()

Yes

 

OCIRef*()

No

 

OCIReset()

No

 

OCIRowidToChar()

Yes

 

OCIServer*()

Yes

OCI_DEFAULT is the only supported value for the mode parameter of OCIServerAttach.

OCISessionBegin()

Yes

OCI_CRED_RDBMS is the only supported value for the credt parameter.

OCI_DEFAULT is the only supported value for the mode parameter.

OCISessionEnd()

Yes

 

OCISessionGet()

Yes

TimesTen does not support switching between sessions.

OCISessionPoolCreate()

No

 

OCISessionPoolDestroy()

No

 

OCISessionRelease()

Yes

 

OCISharedLibInit()

No

 

OCIStmtExecute()

Yes

The following are unsupported values for the mode parameter:

  • OCI_BATCH_ERRORS

  • OCI_STMT_SCROLLABLE_READONLY

Note: Using OCI_COMMIT_ON_SUCCESS results in improved performance, avoiding an extra round trip to the server to commit a transaction.

OCIStmtFetch()

Yes

 

OCIStmtFetch2()

Yes

The only supported values for the orientation parameter are OCI_DEFAULT and OCI_FETCH_NEXT.

OCIStmtGetBindInfo()

Yes

 

OCIStmtGetPieceInfo()

No

 

OCIStmtPrepare()

Yes

The only supported value for the language parameter is OCI_NTV_SYNTAX.

OCIStmtPrepare2()

Yes

The only supported value for the mode parameter is OCI_DEFAULT.

For statement caching, TimesTen supports the key argument to tag a statement for future calls to OCIStmtPrepare2() or OCIStmtRelease().

OCIStmtRelease()

Yes

The only supported value for the mode parameter is OCI_DEFAULT.

For statement caching, TimesTen supports the key argument to tag a statement. This can be the key from OCIStmtPrepare2().

OCIStmtSetPieceInfo()

No

 

OCIString*()

Yes

 

OCISubscription*()

No

TimesTen does not support Advanced Queuing.

OCISvcCtxToLda()

No

 

OCITable*()

No

 

OCITerminate()

No

 

OCIThread*()

Yes

 

OCITransCommit()

Yes

The only supported value for the mode parameter is OCI_DEFAULT.

OCITransDetach()

No

 

OCITransForget()

No

 

OCITransMultiPrepare()

No

 

OCITransPrepare()

No

 

OCITransRollback()

Yes

 

OCITransStart()

No

 

OCIType*()

No

 

OCIUnicodeToCharSet()

Yes

 

OCIUserCallbackGet()

Yes

 

OCIUserCallbackRegister()

Yes

 

OCIWideChar*()

Yes

 

OCIXmlDbFreeXmlCtx()

No

TimesTen does not support XML DB.

OCIXmlDbInitXmlCtx()

No

TimesTen does not support XML DB.


Supported handles and attributes

Table 3-3 lists the handles and attributes that TimesTen OCI supports.

Table 3-3 TimesTen OCI supported handles and attributes

Handle C object Supported attributes

Environment

OCIEnv

OCI_ATTR_ENV_CHARSET_ID

OCI_ATTR_ENV_NCHARSET_ID

OCI_ATTR_ENV_UTF16

OCI_ATTR_EVTCTX

OCI_ATTR_OBJECT

Error

OCIError

OCI_ATTR_DML_ROW_OFFSET

Service context

OCISvcCtx

OCI_ATTR_ENV

OCI_ATTR_IN_V8_MODE

OCI_ATTR_SERVER

OCI_ATTR_SESSION

OCI_ATTR_TRANS

Statement

OCIStmt

OCI_ATTR_BIND_COUNT

OCI_ATTR_CURRENT_POSITION

OCI_ATTR_ENV

OCI_ATTR_FETCH_ROWID

OCI_ATTR_NUM_DML_ERRORS

OCI_ATTR_PARAM_COUNT

OCI_ATTR_PREFETCH_MEMORY

OCI_ATTR_PREFETCH_ROWS

OCI_ATTR_ROW_COUNT

OCI_ATTR_ROWID

OCI_ATTR_ROWS_FETCHED

OCI_ATTR_SQLFNCODE

OCI_ATTR_STATEMENT

OCI_ATTR_STMT_TYPE

Bind

OCIBind

OCI_ATTR_CHARSET_FORM

OCI_ATTR_CHARSET_ID

OCI_ATTR_MAXCHAR_SIZE

OCI_ATTR_MAXDATA_SIZE

Define

OCIDefine

OCI_ATTR_CHARSET_FORM

OCI_ATTR_CHARSET_ID

OCI_ATTR_MAXCHAR_SIZE

Describe

OCIDescribe

OCI_ATTR_PARAM

OCI_ATTR_PARAM_COUNT

Server

OCIServer

OCI_ATTR_ENV

OCI_ATTR_IN_V8_MODE

OCI_ATTR_SERVER_GROUP

OCI_ATTR_SERVER_STATUS

User session

OCISession

OCI_ATTR_CLIENT_IDENTIFER

OCI_ATTR_CLIENT_INFO

OCI_ATTR_CURRENT_SCHEMA

OCI_ATTR_DRIVER_NAME

OCI_ATTR_INITIAL_CLIENT_ROLES

OCI_ATTR_MODULE

OCI_ATTR_PROXY_CREDENTIALS

OCI_ATTR_USERNAME

Authentication

OCIAuthInfo

Same as for user session handle

Transaction

OCITrans

OCI_ATTR_TRANS_NAME

OCI_ATTR_TRANS_TIMEOUT

Thread

OCIThreadHandle

 

Supported descriptors

Table 3-4 lists the descriptors that TimesTen OCI supports.

Table 3-4 TimesTen OCI supported descriptors

Descriptor C object

Parameter (read-only)

OCIParam

ROWID

OCIRowid

ANSI DATE

OCIDateTime

TIMESTAMP

OCIDateTime

TIMESTAMP WITH TIME ZONE

OCIDateTime

TIMESTAMP WITH LOCAL TIME ZONE

OCIDateTime

INTERVAL YEAR TO MONTH

OCIInterval

INTERVAL DAY TO SECOND

OCIInterval

User callback

OCIUcb


Supported SQL data types

Table 3-5 lists the SQL data types that TimesTen OCI supports.

Table 3-5 TimesTen OCI supported SQL data types

SQL data type Notes

SQLT_AFC

 

SQLT_AVC

 

SQLT_BDOUBLE

 

SQLT_BFLOAT

 

SQLT_BIN

 

SQLT_BLOB

 

SQLT_CHR

 

SQLT_CLOB

To write to or read from an NCLOB, set the character set form (csfrm) parameter to SQLCS_NCHAR for applicable function calls.

SQLT_DAT

 

SQLT_DATE

 

SQLT_FLT

 

SQLT_IBDOUBLE

 

SQLT_IBFLOAT

 

SQLT_INT

 

SQLT_INTERVAL_DS

Not stored in TimesTen.

SQLT_INTERVAL_YM

Not stored in TimesTen.

SQLT_LBI

 

SQLT_LNG

 

SQLT_LVB

Truncated at 4 MB when stored in TimesTen.

SQLT_LVC

Truncated at 4 MB when stored in TimesTen.

SQLT_NUM

 

SQLT_ODT

 

SQLT_RDD

Rowids are returned in Oracle Database format.

SQLT_RSET

Only one result set parameter is allowed for each statement.

SQLT_STR

 

SQLT_TIME

 

SQLT_TIME_TZ

Time zone is ignored when stored in TimesTen.

SQLT_TIMESTAMP

 

SQLT_TIMESTAMP_LTZ

Time zone is ignored when stored in TimesTen.

SQLT_TIMESTAMP_TZ

Time zone is ignored when stored in TimesTen.

SQLT_UIN

 

SQLT_VBI

 

SQLT_VCS

 

SQLT_VNU

 

SQLT_VST

 

Supported parameter attributes

Table 3-6 that follows lists supported parameter attributes.

Table 3-6 TimesTen OCI supported parameter attributes

Parameter Supported attributes

All parameters

OCI_ATTR_NUM_PARAMS

OCI_ATTR_OBJ_NAME

OCI_ATTR_OBJ_SCHEMA

OCI_ATTR_PTYPE

Table and view parameters

OCI_ATTR_NUM_COLS

OCI_ATTR_LIST_COLUMNS

PL/SQL procedure and function parameters

OCI_ATTR_LIST_ARGUMENTS

PL/SQL subprogram parameters

OCI_ATTR_LIST_ARGUMENTS

OCI_ATTR_NAME

PL/SQL package parameters

OCI_ATTR_LIST_SUBPROGRAMS

Sequence parameters

OCI_ATTR_OBJID

OCI_ATTR_MIN

OCI_ATTR_MAX

OCI_ATTR_INCR

OCI_ATTR_CACHE

OCI_ATTR_ORDER

OCI_ATTR_HW_MARK

Column parameters

OCI_ATTR_CHAR_USED

OCI_ATTR_CHAR_SIZE

OCI_ATTR_DATA_SIZE

OCI_ATTR_DATA_TYPE

OCI_ATTR_NAME

OCI_ATTR_PRECISION

OCI_ATTR_SCALE

OCI_ATTR_IS_NULL

OCI_ATTR_TYPE_NAME

OCI_ATTR_SCHEMA_NAME

OCI_ATTR_CHARSET_ID

OCI_ATTR_CHARSET_FORM

Argument and result parameters

OCI_ATTR_NAME

OCI_ATTR_POSITION

OCI_ATTR_DATA_TYPE

OCI_ATTR_DATA_SIZE

OCI_ATTR_PRECISION

OCI_ATTR_SCALE

OCI_ATTR_LEVEL

OCI_ATTR_IS_NULL

OCI_ATTR_CHARSET_ID

OCI_ATTR_CHARSET_FORM

List parameters

OCI_LTYPE_COLUMN

OCI_LTYPE_SCH_OBJ

OCI_LTYPE_DB_SCH

Database parameters

OCI_ATTR_VERSION

OCI_ATTR_CHARSET_ID

OCI_ATTR_NCHARSET_ID

OCI_ATTR_LIST_SCHEMAS

OCI_ATTR_MAX_PROC_LEN

OCI_ATTR_MAX_COLUMN_LEN

OCI_ATTR_ATTR_CURSOR_COMMIT_BEHAVIOR

OCI_ATTR_MAX_CATALOG_NAMELEN

OCI_ATTR_CATALOG_LOCATION

OCI_ATTR_SAVEPOINT_SUPPORT

OCI_ATTR_NOWAIT_SUPPORT

OCI_ATTR_AUTOCOMMIT_DDL

OCI_ATTR_LOCKING_MODE