3 TimesTen Support for Oracle Call Interface

Character sets

To specify a character set for the connection, OCI programs can set the NLS_LANG environment variable or call OCIEnvNlsCreate(). Any connection character set in the odbc.ini file is ignored. 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 include the period in front of the character set when setting NLS_LANG. For example, either of the following would work, although AMERICAN_AMERICA is ignored:

NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Or:

NLS_LANG=.WE8ISO8859P1

Additional globalization features

TimesTen OCI also supports the following additional globalization features. These can be set either as environment variables or TimesTen general connection attributes. An environment variable setting takes precedence.

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

• NLS_SORT: This specifies the type of sort for character data. It overrides the default value from NLS_LANGUAGE. Valid values are BINARY or any linguistic sort name supported by TimesTen. For example, to specify the German linguistic sort sequence, set NLS_SORT=German. NLS_NCHAR_CONV_EXCP: This determines whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR or NVARCHAR data and CHAR or VARCHAR2 data. Valid settings are TRUE and FALSE. The default value is FALSE, resulting in no error being reported.

Refer to Oracle TimesTen In-Memory Database Operations Guide and Oracle Database Globalization Support Guide for additional information on these environment variables and related features.

Oracle Database features not supported

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

• Advanced Queuing

• Any Data

• Object support

• LOB data types

• Collections

• Cartridge Services

• Direct path loading

• Date/time intervals

• Iterators

• BFILE

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

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

• The DDLCommitBehavior general connection attribute must be set to 0, which corresponds to Oracle 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.

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

• The TNSPING utility does not recognize connections to TimesTen.

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

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

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

• Binding and defining of structures through OCIBindArrayOfStruct() and OCIDefineArrayOfStruct() is supported for SQL statements but not for PL/SQL.

• Oracle utilities such as SQL*Plus and SQL*Loader are not supported. (As alternatives for these two in particular, you can use the ttIsql utility and the ttBulkCp built-in procedure, respectively.)

• Array binding, the ability to bind associative arrays (index-by tables) and varrays (variable size arrays) into PL/SQL statements, is not supported.

Additional TimesTen OCI differences

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

With OCI, TimesTen automatically disables autocommit for DML statements.

Using the tnsnames naming method to connect

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

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

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

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

DESCRIPTION and CONNECT_DATA are required as shown.

For SERVICE_NAME, dsn must be a TimesTen DSN that is configured in the odbc.ini or sys.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. In client/server mode, 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 desired, 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 the DSN ttclient in the 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 odbc.ini:

"localhost/ttdirect:timesten_direct"

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

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

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

For example:

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

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

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

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

Configuring whether to use tnsnames.ora or easy connect

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

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

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

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

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

Connecting as an externally identified user in OCI

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

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

Adapting an earlier example:

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

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

Specifying the Oracle password in OCI for IMDB Cache

To use IMDB Cache, there must be a cache user in the TimesTen database with the same name as an Oracle Database user who can select from and update the cached Oracle tables. This Oracle 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 user with the same name. See "Setting Up a Caching Infrastructure" in Oracle In-Memory Database Cache User's Guide for details.

For use of OCI with the IMDB Cache, TimesTen allows you to pass the Oracle 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 user's password is the same as the TimesTen user's password.

Note the following for the example:

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

• ttpwd is the password of the TimesTen cache user.

• orclpwd is the password of the Oracle user.

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

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

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

Determining the number of cache groups affected by an action

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

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