Overview of TimesTen OCI Support
You can use OCI with TimesTen. For supported features, TimesTen OCI syntax and usage are the same as in Oracle Database.
These 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.
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 theNLS_LANG
setting underHKEY_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 typesCHAR
andVARCHAR2
are specified in bytes, not characters. For single-byte character encoding this works well. For multibyte character encoding, you can useNLS_LENGTH_SEMANTICS
to createCHAR
andVARCHAR2
columns using character-length semantics instead. Supported settings areBYTE
(default) andCHAR
. (NCHAR
andNVARCHAR2
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 fromNLS_LANG
. Valid values areBINARY
or any linguistic sort name supported by TimesTen. For example, to specify the German linguistic sort sequence, setNLS_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 betweenNCHAR
orNVARCHAR
data andCHAR
orVARCHAR2
data. Valid settings areTRUE
andFALSE
. The default value isFALSE
, 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 fromINSERT
,UPDATE
, andDELETE
statements is not supported. (This is supported forSELECT 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()
andOCIDefineArrayOfStruct()
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 andttBulkCp
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.