Overview of TimesTen OCI Support

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.

TimesTen OCI Basics

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

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

TimesTen Release 22.1 OCI is based on Oracle Database release 19c OCI and supports the contemporary OCI 8 style APIs.

OCI in the TimesTen Architecture

OCI support is positioned in the TimesTen architecture.

Figure 3-1 shows where OCI support is positioned in the TimesTen architecture.

Figure 3-1 OCI in the TimesTen Architecture

Description of "Figure 3-1 OCI in the TimesTen Architecture"

Globalization Support in TimesTen OCI

TimesTen OCI supports globalization.

About TimesTen Support for Character Sets

TimesTen character sets are compatible with Oracle Database.

The TimesTen default character set is AMERICAN_AMERICA.US7ASCII (but AL32UTF8 is recommended as an alternative). Refer to Supported Character Sets in Oracle TimesTen In-Memory Database Reference.

Specifying a Character Set

To specify a character set for the connection, OCI programs can set the NLS_LANG environment variable or call OCIEnvNlsCreate().

The ConnectionCharacterSet 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, but AL32UTF8 is recommended as an alternative.

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

Note:

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.
Refer to OCIEnvNlsCreate() in Oracle Call Interface Programmer's Guide.

Additional Globalization Features

TimesTen OCI also supports the globalization features referenced here. 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, Setting Up a Globalization Support Environment in Oracle Database Globalization Support Guide and Attribute Support for ODBC 3.5 SQLSetConnectAttr and SQLGetConnectAttr.

TimesTen Restrictions and Limitations

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

Oracle Database Features Not Supported by TimesTen

TimesTen does not support OCI calls that are related to functionality that does not exist in TimesTen, which 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

TimesTen OCI Limitations

There are certain restrictions in TimesTen OCI.

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 Binding Associative Arrays in TimesTen OCI.)
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.