Oracle® Data Provider for .NET

Environments and TimesTen releases supported by ODP.NET

This revision of the document is for TimesTen support of the ODP.NET 12.1 release.

Note the following:

• You must have a TimesTen 11.2.2 release to use ODP.NET 12.1 with TimesTen. See "ODP.NET namespace and class support with TimesTen" for details of supported namespaces and APIs. ODP.NET 12.1 is available in corresponding Oracle Database or Oracle Data Access Components (ODAC) releases.

  Note:

  TimesTen release 11.2.2.4.0 or higher is recommended.

• As of this release, ODP.NET for TimesTen can be used in the following environments:

  • ODP.NET for .NET Framework 2.0 with Microsoft .NET Framework 3.5 SP 1 or higher

  • ODP.NET for .NET Framework 4 with Microsoft .NET Framework 4 or 4.5

• ODP.NET for TimesTen can be used on all Microsoft Windows 32-bit and 64-bit platforms that support TimesTen. The 32-bit version of ODP.NET must be used with a 32-bit instance of the TimesTen database or TimesTen client. Likewise, the 64-bit version of ODP.NET must be used with a 64-bit instance of the TimesTen database or TimesTen client.

Support for .NET-related features

ODP.NET for TimesTen supports a subset of features currently available in ODP.NET for Oracle Database. In particular, as of this release, it supports the following features:

• ODP.NET connection pooling

• ODP.NET tracing

ODP.NET for TimesTen does not currently support these features:

• ADO.NET Entity Framework object relational mapper

• LINQ (Language-Integrated Query)

ODP.NET for TimesTen does not currently support interoperability with the following Oracle Database client components:

• Oracle Developer Tools for Visual Studio

• Oracle Database Extensions for .NET

• Oracle Providers for ASP.NET

Requirements and prerequisites for using ODP.NET with TimesTen

Note the following requirements to use ODP.NET for TimesTen:

• You must install TimesTen Data Manager or TimesTen Client or both on your system. TimesTen is not provided with ODP.NET or OCI.

• PL/SQL must be installed and enabled. It is installed by default during TimesTen installation. It is also enabled by default, or can be enabled through the first connection attribute setting PLSQL=1 or the ODBC Data Source Administrator.

• ODP.NET 12.1 for TimesTen depends on Oracle Call Interface (OCI) support for TimesTen and requires the version of OCI that is provided with ODP.NET 12.1 releases, not the version provided with TimesTen.

  Also see "Post-installation path considerations".

  Notes:

  • For reference, the OCI version provided with TimesTen is under the tt_install_dir\ttoracle_home directory, where tt_install_dir is the TimesTen installation root directory. Do not use this version for ODP.NET applications.

  • Of course there is no issue in using the TimesTen version of OCI for OCI or Pro*C/C++ programs that do not use ODP.NET.

• Requirements for the execution environment to use ODP.NET with Oracle Database apply to using ODP.NET with TimesTen as well. Refer to Oracle Data Provider for .NET Developer's Guide for information.

Related documents

Some of the preceding discussion refers to documents in the Oracle TimesTen In-Memory Database and Oracle Database documentation libraries.

TimesTen documentation is available on the product distribution media, and on the Oracle Technology Network at the following location.

http://www.oracle.com/technetwork/database/database-technologies/timesten/documentation/index.html

Oracle documentation is also available on the Oracle Technology network, at the following location.

http://www.oracle.com/pls/db121/homepage

Installing ODP.NET

This section covers the following installation:

• Install ODP.NET as part of Oracle Database 12.1

• Install ODP.NET as part of ODAC 12.1 for Windows, OUI version

• Install ODP.NET as part of ODAC 12.1 for Windows, XCopy version

The installation process for ODP.NET is independent of the TimesTen environment. Nothing is installed into the TimesTen installation directories.

Refer to Oracle Data Provider for .NET Developer's Guide for additional information about ODP.NET installation, including associated Windows registry entries.

install.bat odp.net2 C:\oracle\odac odachome

install.bat odp.net4 C:\oracle\odac odachome

install.bat all C:\oracle\odac odachome

Post-installation path considerations

In a TimesTen environment, ODP.NET finds and uses the appropriate version of OCI; namely, the Oracle Client version and not the TimesTen Instant Client version. In addition, check the following for your path:

1. Confirm that the PATH setting has the location of the TimesTen shared libraries at tt_install_dir\bin, where tt_install_dir is the TimesTen installation root directory. This should follow any other Oracle directories in the path.

2. For an XCopy installation, add your ODAC installation directory and ODAC installation bin directory to the PATH setting, preceding any other Oracle directories, including TimesTen directories. For example, if C:\oracle\odac is the installation directory:

   set PATH=C:\oracle\odac;C:\oracle\odac\bin;%PATH%
   

Uninstalling ODP.NET

For information about uninstalling Oracle Database products, including ODP.NET, refer to "Removing Oracle Database Software" in Oracle Database Installation Guide for Microsoft Windows.

To uninstall an OUI installation, run setup.exe again (refer to "Install ODP.NET as part of ODAC 12.1 for Windows, OUI version"). In the OUI welcome page, choose Deinstall Products. In the resulting Inventory dialog, select the product or products to uninstall, then choose Remove. Then choose Yes in the Confirmation and Warning dialogs. Close the Inventory dialog once the products have been uninstalled.

To uninstall an XCopy installation, execute the uninstall.bat batch file from your ODP.NET installation directory (refer to "Install ODP.NET as part of ODAC 12.1 for Windows, XCopy version"), specifying the product to uninstall (or all products) and the Oracle home name for ODAC products. For example, to uninstall a client with ODP.NET for .NET 2.0 libraries, assuming the Oracle home name is odachome:

uninstall.bat odp.net2 odachome

Or to uninstall all ODAC products:

uninstall.bat all odachome

Building an application for ODP.NET

You can use the Visual Studio IDE to build your application, or you can use the csc.exe command-line compiler executed from the Visual Studio command prompt. The following example uses csc.exe (where the input is all one command line):

C:\Temp> csc /out:myapp.exe /reference:C:\app\mydir\path\
Oracle.DataAccess.dll myapp.cs

Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

The location of the Oracle.DataAccess.dll assembly and dependent libraries is according to your type of Oracle product installation and version of .NET. Refer to Oracle Data Provider for .NET Developer's Guide and the ODP.NET README file for information.

Using the tnsnames naming method to connect

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

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

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

Where tns_entry is an arbitrary TNS name you assign to the entry. Note the following:

• DESCRIPTION and CONNECT_DATA are required as shown.

• For SERVICE_NAME, dsn must be a TimesTen data source name (DSN) that is defined in the ODBC Data Source Administrator and is visible to the user running the ODP.NET application.

• For SERVER, timesten_direct specifies a direct connection to a TimesTen database, while timesten_client specifies a client/server connection. If you specify timesten_direct, then dsn must be a TimesTen Data Manager DSN. If you specify timesten_client, then dsn must be a TimesTen Client DSN.

The following is a sample tnsnames.ora entry for a direct connection to the TimesTen database referenced by the DSN my_dsn:

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

To connect as user scott with password tiger to the my_dsn TimesTen database that is referenced by the my_tnsname entry in the tnsnames.ora file, specify the following connection string in your ODP.NET application:

"User Id=scott;Password=tiger;Data Source=my_tnsname"

To connect as the current operating system user to my_dsn that is referenced by the my_tnsname entry in the tnsnames.ora file, specify the following connection string in your ODP.NET application. The current operating system user must be either the TimesTen instance administrator or a defined TimesTen external user.

"User Id=/;Data Source=my_tnsname"

Using the easy connect naming method to connect

TimesTen supports easy connect syntax, which allows connections to be made without configuring a tnsnames.ora entry. The syntax of a TimesTen easy connect string is as follows:

host/service_name:server

Note the following:

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

• For service_name, specify a TimesTen DSN that is defined in the ODBC Data Source Administrator and is visible to the user running the ODP.NET application.

• For server, timesten_direct specifies a direct connection to a TimesTen database, while timesten_client specifies a client/server connection. If you specify timesten_direct, then service_name must be a TimesTen Data Manager DSN. If you specify timesten_client, then service_name must be a TimesTen Client DSN.

To establish a direct connection as user scott with password tiger to the TimesTen database referenced by the my_dsn DSN, specify the following connection string in your ODP.NET application:

"User Id=scott;Password=tiger;Data Source=localhost/my_dsn:timesten_direct"

To establish a direct connection as the current operating system user to the TimesTen database referenced by my_dsn, specify the following connection string in your ODP.NET application. The current operating system user must be either the TimesTen instance administrator or a defined TimesTen external user.

"User Id=/;Data Source=localhost/my_dsn:timesten_direct"

Configuring whether to use tnsnames or easy connect naming method

If a sqlnet.ora file is present, it specifies the naming methods to be tried and the order in which to try them. ODP.NET looks for a sqlnet.ora file with the following precedence:

1. If the TNS_ADMIN environment variable has been set, ODP.NET looks in that specified location.

2. If TNS_ADMIN has not been set, ODP.NET looks in 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, you can use only naming methods that are indicated there. If sqlnet.ora is not found, you can use either the tnsnames or easy connect naming method.

In TimesTen, sample copies of the tnsnames.ora and sqlnet.ora files are in the tt_install_dir\network\admin\samples directory, where tt_install_dir is the TimesTen installation root directory. The following is the sqlnet.ora file that TimesTen provides, which supports both the tnsnames naming method and the easy connect naming method.

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

With this setting, ODP.NET first looks for tnsnames syntax in your connection strings. If it cannot find tnsnames syntax, it looks for easy connect strings.

Setting TimesTen connection attributes in ODP.NET connection strings

You can set TimesTen connection attributes within the Password setting of your ODP.NET connection string, with syntax as follows:

• Components of the Password setting, including the password setting itself and any TimesTen connection attribute settings, are delimited by semi-colons.

• Whenever the Password setting has semi-colons, the entire setting must be quoted.

• Because the ODP.NET connection string as a whole is quoted, the begin quotation mark and end quotation mark of the Password setting must each be preceded by the "\" escape character.

The following example specifies lion as the password for user scott in TimesTen. It also sets the TimesTen OraclePWD connection attribute, which specifies the password tiger for user scott in Oracle Database, for use of TimesTen Application-Tier Database Cache (TimesTen Cache).

"Data Source=mysource;User Id=scott;Password=\"lion;OraclePwd=tiger\"";

The next example again specifies lion as the password for scott in TimesTen. This time, it sets the TimesTen OracleNetServiceName connection attribute as well as the OraclePWD connection attribute. OracleNetServiceName specifies the Oracle ID in Oracle Database, with the OraclePWD setting specifying the corresponding password tiger. Finally, this example sets the TimesTen passthrough level to 1.

"Data Source=mysource;User ID=scott;Password=\"lion;OraclePwd=tiger;
OracleNetServiceName=mytest-pc.example.com;passthrough=1\"";

(For general information about TimesTen connection attributes, refer to "Connection Attributes" in Oracle TimesTen In-Memory Database Reference.)

Effects of transaction commits on result sets and REF CURSORs

When processing result sets generated from executing statements and creating REF CURSORs, the behavior when transactions in TimesTen connections are committed differs from that when transactions in Oracle Database connections are committed. When a transaction is committed in TimesTen while a result set of an OracleDataReader object is open, the result set is closed automatically, unlike in an Oracle database. This applies to explicit commits, autocommit, and implicit commits.

In TimesTen, an implicit commit occurs after a DDL statement. In ODP.NET, an implicit commit also occurs when an OracleCommand object is executed without there first being an OracleTransaction object instantiated from the command connection. An explicit commit occurs when the Commit method is called on an OracleTransaction object. In either case, if a commit occurs in a TimesTen connection before a result set that is open in the transaction is completely processed, the "Function sequence error" exception may be thrown.

This difference in behavior is likely to occur when the execution of an OracleCommand object is interleaved with the processing of a result set associated with another OracleCommand object. To avoid the "Function sequence error" exception, the execution and processing of a result set should be contained exclusively within the context of an OracleTransaction object. This prevents a commit from occurring before all rows of the result set are retrieved.

The occurrence of a "Function sequence error" exception may depend on the value of the FetchSize property of an OracleCommand, OracleRefCursor or OracleDataReader object. If the FetchSize property is not explicitly set or if it is set to a large value, then many rows may be fetched by the application before the "Function sequence error" exception is thrown.

Support for TimesTen built-in procedures

You can call TimesTen built-in procedures directly from TimesTen OCI only for built-ins that do not return a result set. Therefore, this restriction also applies to ODP.NET for TimesTen.

Use an OracleCommand instance to call a built-in, as in the following example. This assumes an OracleConnection instance conn with a connection to TimesTen has been established. Call the Dispose method to free resources when you have finished using the OracleCommand instance.

// switching to passthrough 1 mode using ttOptSetFlag built-in function
string switchModeStmt = "call ttOptSetFlag('passthrough', 1)";
OracleCommand switchCmd = new OracleCommand(switchModeStmt, conn);
switchCmd.CommandType = CommandType.Text;
switchCmd.ExecuteNonQuery();
switchCmd.Dispose();

For built-in procedures that do return a result set, the result set would not be accessible directly through ODP.NET. However, you could access it as an OUT parameter if you call the built-in from PL/SQL. Here is an example:

int passThruValue = -1;
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "declare v_name varchar2(255); begin execute immediate 
                  'call ttOptGetFlag(''passthrough'')' into v_name, :rc1; end;";
cmd.Parameters.Add("rc1", OracleDbType.Int32, -1, ParameterDirection.Output);
cmd.ExecuteNonQuery();
passThruValue = Convert.ToInt32(cmd.Parameters[0].Value.ToString());
cmd.Parameters.Clear();
cmd.Dispose();

Support for VARCHAR2, NVARCHAR2 and VARBINARY data types

TimesTen VARCHAR2, NVARCHAR2 and VARBINARY types support a maximum of 4 MB of data. ODP.NET for TimesTen supports the transfer of the maximum amount of data for these types (and all other TimesTen SQL types).

Support for LOBs

TimesTen LOB support is limited to the LOB access methods associated with the default 0 (zero) setting of the InitialLobFetchSize property of the OracleDataReader object. If this property is changed to another value then TimesTen ignores such changes, assuming it retains the 0 setting.

Also refer to "OracleDataReader class support".

Known issues and limitations

This section discusses limitations that are known as of release time.

• The self-tuning statement cache disables itself if it detects that not enough memory is available for its operations. Note that when an application uses a direct (as opposed to client/server) connection to TimesTen, the entire database is loaded into memory, thereby reducing memory available for the statement cache and making this behavior more likely.

Troubleshooting

This section discusses solutions for various exceptions you may encounter.

• Exception "ORA-44818: PL/SQL feature not installed in this TimesTen database"

  If PL/SQL is disabled for the TimesTen database (it is enabled by default), enable it by setting the PLSQL connection attribute to 1 in the ODBC Data Source Administrator. ODP.NET applications cannot connect to TimesTen databases that are not PL/SQL-enabled.

• Exception "TimesTen database version is less than the minimum required version of 11.2.2.0.0"

  Change the service name in the tnsname.ora entry or easy connect string to reference a TimesTen 11.2.2 database. ODP.NET 12.1 applications cannot connect to TimesTen database versions prior to 11.2.2.

• Exception "ORA-12154: TNS: Could not resolve the connect identifier specified" or "ORA-12541: TNS: No listener"

  To connect to a TimesTen database from an ODP.NET application, the Data Source attribute in the ODP.NET connection string must be set either to the TNS name of a TimesTen entry in the tnsnames.ora file or to a TimesTen easy connect string.

  If the tnsnames naming method is used to connect, verify that an entry in the tnsnames.ora file is associated with a TimesTen DSN. Also verify that the TNS_ADMIN environment variable is set to the directory where the tnsnames.ora file is located.

  If the easy connect naming method is used to connect, verify that service_name is set to a TimesTen DSN and that server is set to either timesten_direct or timesten_client, depending on whether the DSN configures a direct connection or a client/server connection.

• Exception "ORA-29158: Unable to open library"

  If you are connecting to a TimesTen database, verify either that the entry in the tnsnames.ora file is associated with a TimesTen DSN or that service_name in the easy connect string is set to a TimesTen DSN.

  This error may also occur due to a path issue, if ODP.NET cannot find the TimesTen ODBC driver, which is located in the TimesTen tt_install_dir\bin directory. (Also see the next troubleshooting item.)

• Exception "ORA-29159: Unable to read library"

  In addition to the steps for ORA-29158 above, verify that the server setting in the tnsnames.ora file entry or easy connect string is timesten_direct or timesten_client, as appropriate for the type of TimesTen DSN.

  See "Connecting to a TimesTen database from OCI" in Oracle TimesTen In-Memory Database C Developer's Guide for information about tnsnames.ora and easy connect.

• Exception "The application has failed to start because ttcommonxxxx.dll was not found. Re-installing the application may fix the problem"

  This indicates that the location of the TimesTen shared libraries at tt_install_dir\bin is not in the PATH environment variable setting.

  Note:

  Instead of "xxxx", the TimesTen release number is indicated. In TimesTen 11.2.2 releases the file name is ttcommon1122.dll.

The ttSrcScan utility

If you have an existing ODP.NET application and want to see whether it uses ODP.NET 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.

Oracle.DataAccess.Client namespace support

The following tables list support for delegates, classes, and enumerations of the Oracle.DataAccess.Client namespace.

The rest of this section presents the following:

• OracleCommand class support

• OracleConnection class support

• OracleDataReader class support

• OracleTransaction class support

public OracleConnectionType ConnectionType

OracleConnectionType.Undefined: No connection is associated with the OracleConnection object

OracleConnectionType.Oracle: The OracleConnection object is associated with an Oracle database

OracleConnectionType.TimesTen: The OracleConnection object is associated with a TimesTen database

Oracle.DataAccess.Types namespace support

The following tables list support for structures, exceptions, classes, interfaces, and enumerations of the Oracle.DataAccess.Types namespace.