ODP.NET minimal, non-intrusive install

This might be of interest for those who use .NET to connect to Oracle databases. (Including yours truly, who wrote the Thoth Gateway, a mod_plsql replacement that runs on Microsoft IIS, using C# and ODP.NET.)

A while back, Microsoft officially deprecated their ADO.NET driver for Oracle (System.Data.OracleClient).

Fortunately, Oracle offers its own .NET driver, known as the Oracle Data Provider for .NET (ODP.NET). This driver is a better choice for Oracle connectivity, since it supports a wider range of Oracle-specific features, and improved performance.

However, ODP.NET, unlike, say, the thin JDBC drivers, still requires the normal Oracle client to be present on the machine. This Oracle client can be something of a beast, with the install package upwards of 200 megabytes. Couple this with the fact that you may have several diffent Oracle client versions installed on your machine (or application server), all specific to some application that you dare not touch for fear of it breaking.

A non-intrusive install

So, here is how you can use ODP.NET with the following advantages:

• Small footprint (between 30 and 100 megabytes)
• XCopy deployment
• No dependency on shared files, all files in your own application's folder
• No registry or system environment changes required
• No tnsnames.ora file required
• No interference from other Oracle client installs on the same machine

Sounds good, doesn't it? Let's see how this can be accomplished...

1. Download ODP.NET (xcopy version)

Download from here:

http://www.oracle.com/technology/software/tech/windows/odpnet/utilsoft.html

Unzip the file and locate the following 2 files:

• OraOps11w.dll
• Oracle.DataAccess.dll

Copy these files to your application's "bin" folder.

2. Download Oracle Instant Client

Download from here:

http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

You have a choice between the following two versions of the Instant Client

a) Instant Client Basic (approx. 100 megabytes)

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociei11.dll

b) Instant Client Basic Lite (approx. 30 megabytes): This version is smaller but only supports certain character sets (WE8MSWIN1252 and AL32UTF8 are among them). It only has English messages, so in case you wonder what "ORA-06556: The pipe is empty" sounds like in your own language, go for the non-Lite version.

Unzip the file and locate the following 3 files:

• oci.dll
• orannzsbb11.dll
• oraociicus11.dll

Whichever version you choose, copy these files to your application's "bin" folder. You now have a total of 5 new files in your "bin" folder.

3. Connection string

In your .NET program, use a connect string in the following format, to make sure you don't need to rely on any network configuration files (tnsnames.ora, etc.).

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=database_host_name)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=database_service_name)))

4. Configuration

This is mostly relevant if you have other Oracle client installations already on the same machine/server.

In your configuration file (web.config), you can explicitly set the path to the Oracle DLLs you want to use. Set the "DllPath" parameter to the name of your "bin" folder.

<configuration>
<oracle.dataaccess.client>
<settings>
<add name="DllPath" value="c:\my_app_folder\bin"></add>
<add name="FetchSize" value="65536"></add>
<add name="PromotableTransaction" value="promotable"></add>
<add name="StatementCacheSize" value="10"></add>
<add name="TraceFileName" value="c:\temp\odpnet2.log"></add>
<add name="TraceLevel" value="0"></add>
<add name="TraceOption" value="0"></add>
</settings>
</oracle.dataaccess.client>
</configuration>

5. That's it!

You should now be able to run your ODP.NET application from your "bin" folder.



References

• Installing Oracle Data Provider for .NET
• ODP.NET Configuration
• http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html