How to determine which version of "Oracle.DataAccess.dll" to include

Question

While deploying my .NET 3.5 Windows form to different environments we ran with lots of invalid provider issues.

It works on some and doesn't work on others.

Could someone please help me out, how do I determine which version of "Oralce.DataAccess.dll" to use i.e. 9 or 10 or 11 or 9.1.* or 10.1.* or 11.* or 12 etc.

1. Does it depend on server where I'm installing an application? OR

2. Does it depend on back end oracle database?

Answer 1

I second the notion of using the 100% managed provider. It eliminates the need to know details I'm about to discuss. The only issue here is I think you might need to upgrade to .net 4.0.

TLDR Version:

• Use the 12c 100% managed provider instead.
• The short answer is don't mix the provider (Oracle.DataAccess.dll) with a different version of the unmanaged client (at least not backwards).
• Consider redesigning to include a service layer that eliminates the need to have the Oracle provider on the client in the first place.

Full version:

First, lets make sure we understand the components of the old unmnaged provider (not the new 12c 100% managed provider). It's made up of two pieces:

1. the managed .net component - Oracle.DataAccess.dll
2. the unmanaged (non-.net) client

Simply speaking, Oracle.DataAccess.dll is nearly just a wrapper, translating .net instructions into ORACLE-NET instructions for the unmanaged client.

That said, when you load Oracle.DataAccess there is a order in which it tries to locate the unmanaged client dlls that it needs. From the Oracle Documentation:

The Oracle.DataAccess.dll searches for dependent unmanaged DLLs (such as Oracle Client) based on the following order:

1.Directory of the application or executable.

2.DllPath setting specified by application config or web.config.

3.DllPath setting specified by machine.config.

4.DllPath setting specified by the Windows Registry.

HKEY_LOCAL_MACHINE\Software\Oracle\ODP.NET\version\DllPath

5.Directories specified by the Windows PATH environment variable.

This comes into play if you have more than one client installed on the machine so this could be part of your issue. If so, the simple thing to do is use the dllPath configuration variable in your config:

<configuration>
  <oracle.dataaccess.client> 
    <add key="DllPath" value="c:\oracle\product\1.1.0-xcopy-dep\BIN"/>
  </oracle.dataaccess.client>
</configuration>

Now, to answer your question directly - I don't believe Oracle supports mismatching Oracle.DataAccess.dll with it's client (at least not backwards). Your best bet is either to install ODP.net with your app install - the xcopy version is the smallest and contains the "instant client" Or, you should be thinking about minimum system requirements - ie. The system must have at least version X of odp.net installed. You could then compile against that minimum dll and rely on publisher policy redirection when a target system has a NEWER version of the client.

Of course this also prompts me to ask about architecture. Do you plan on prompting the user for their Oracle account? If not, you have to be careful on how you protect the shared service account your application will use. You may be better off making calls to a webservice that makes the oracle calls on the clients behalf - giving you another security layer and simplifying your client deployment.

Most version of ODP.net are backwards compatible with the database server - you can certainly use the 11g provider with a 10g database.