Wednesday, January 27, 2010

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

10 comments:

Mike Frost said...

Hi Morten, after reading your blog entry, which is interesting an informative, I thought I would reach out to you and see if you were aware of and had tested with the Connect for ADO.NET provider from Progress DataDirect? The Connect provider is 100% managed, meaning that you could skip the step of installing the 30-100MB Oracle client altogether. In addition, the Connect provider delivers better performance while using less memory and supports the Entity Framework.

If you're interested in learning more, free copies of the Connect for ADO.NET provider are available for download at the DataDirect website (http://www.datadirect.com/products/net/net_for_oracle/index.ssp). I invite you to evaluate it and publish your thoughts on how it compares with the provider from ODP.NET

Thank you,
Mike Frost
Progress DataDirect

Morten Braten said...

@Mike: Thanks for the tip, this looks interesting. While I am unlikely to throw out ODP.NET now, I will certainly evaluate your product when I have some free time.

Jay said...

Any idea if a 64-bit version of both the .NET and Oracle Client libraries are available for 64-bit?

Morten Braten said...

@Jay: I see there is a file called "Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64)" on this page: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html

And the "64-bit Oracle10g Release 2 ODAC for Windows x64" is on this page: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Both are first hits on a Google search for these terms, by the way... :-)

Unknown said...

Hi Morten!
I really lika your approach to this problem and I tried to follow your instructions, but failed when it came to the configuration part.
I am using .NET 4 and WPF.
I got an Exception saying:

Unrecognized configuration section oracle.dataaccess.client

Any ideas what I'm doing wrong.

Thanks / Michael

David said...

Hi Michael,

It sounds like you don't have any Oracle installations on your server. This means that the global machine.config file won't get updated to have the oracle.dataaccess.client section registered.

You can fix this by adding it to your web.config file.

<configuration>
<configSections>
<section name="oracle.dataaccess.client" type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</configSections>
</configuration>

Hope that helps.
David

espen said...

Hello,

I discovered a problem when using this approach on a computer with a newer version of ODP already installed.

The GAC wins, and the newer Oracle.DataAccess.dll was loaded. The DllPath in config still has the final word as to where the native dll's should be loaded from. Thus there will be a mismatch between the managed and native dll's - and ODP won't work (an exception is raised).

My solution is to avoid using the GAC. Problem is: the GAC always win! It can't be turned off. Solution is to remove the strong name from the bundled Oracle.DataAccess.dll - the GAC relies on strong naming.

The strong name can be removed easily with a tool like Strong Name Remove. It is generally not recommended to do, but as long as you trust the dll, you shuld be fine.

Strong Name Remove
http://www.codeproject.com/KB/security/StrongNameRemove20.aspx

Rahul Mehta said...

I tried the steps you gave and it works fine for windows application.
when i tried for web (asp.net and iis7.5) its not working...

Please guide!!

Anonymous said...

Currently our web applications are installed on Windows 2003 Server, IIS 6.0. Yes, this seems like the dark ages...years behind. They have an older version of the Oracle Client on the machine (11.0.1). This version does not support Oracle.DataAccess 4.x. .NET Framework 4.0 is installed on the Windows 2003 server. This version also does not support the Entity Framework.

However, my development environment does not have any Oracle Client installed. I am just using the latest version of ODAC/ODP.NET with Visual Studio 2010. I am able to create apps locally with the Entity Framework but unfortunately cannot get them to work on the server because there of the older Oracle Client on that machine. Also there are approx. 50 applications on that server that are accessing this old version of OracleDataAccess 2.116.0 with references hard coded in the web.config. What's the best practice for dealing with legacy applications that are accessing older versions of Oracle?

In short, what do I need to do in order to upgrade to the version of Oracle that will support the Entity Framework while still supporting legacy apps? Do I go for an Oracle Client upgrade or I just merely get the latest version of ODAC/ODP.NET installed on that server?

Morten Braten said...

@Anonymous: "my development environment does not have any Oracle Client installed. I am just using the latest version of ODAC/ODP.NET with Visual Studio 2010"

Unless you are using the ODP.NET "Managed Driver" (which is currently in Beta), you must always have an Oracle Client on any machine running (Unmanaged) ODP.NET.

"What's the best practice for dealing with legacy applications that are accessing older versions of Oracle?"

I suggest you leave them alone, and "sandbox" any new applications with their own set of assemblies, as described in this blog post.


"what do I need to do in order to upgrade to the version of Oracle that will support the Entity Framework while still supporting legacy apps?"

I have no personal experience with Entity Framework, but as far as I can tell, the new ODP.NET Managed Driver will work with EF.

http://www.oracle.com/technetwork/database/windows/downloads/odpmbetainstall-1696475.html

As mentioned, the Managed Driver is still in beta, but you may want to look into it to simplify deployment of new apps:

"ODP.NET, Managed Driver is 100% fully managed. Developers can deploy a single assembly, side by side with other ODP.NET versions easily in a deployment package smaller than 10 MB."

http://www.oracle.com/technetwork/topics/dotnet/whatsnew/index.html


- Morten