8

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?

Kiquenet
  • 14,494
  • 35
  • 148
  • 243
Nil Pun
  • 17,035
  • 39
  • 172
  • 294
  • 1
    What do you mean exactly by issues and that it doesn't work? What kind of errors do you encounter? Is the application server the same machine as the database server or do you install Oracle client on the application server? – metalheart Jan 22 '14 at 07:26

6 Answers6

6

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.

Community
  • 1
  • 1
b_levitt
  • 7,059
  • 2
  • 41
  • 56
  • 1
    Thank you. How do I find out the current version of oracle client installed? – Nil Pun Jan 23 '14 at 21:18
  • 1
    A few suggestions to finding the current client version: http://stackoverflow.com/questions/1171643/whats-the-best-way-to-determine-which-version-of-oracle-client-im-running – b_levitt Jan 24 '14 at 17:39
  • 1
    Ok, here's an interesting behaviour. My .NET Win App runs using "Oracle.DataAccess.dll" version 11 on my laptop and UAT Server. However, it only works with "oracle.Data.Access.dll" version 9 on Production server. What am I doing wrong? Need upgrade ODP.NET in production or something else? – Nil Pun Jan 24 '14 at 22:57
  • 1
    Why does this surprise you? It simply sounds like you have a very old version of the client on the server. Publisher policies let you run against a newer version than you referenced, but I doubt that there's a policy to go back 2 major versions. You could upgrade on the server or use the managed provider. – b_levitt Jan 27 '14 at 18:15
4

First of all clarification: You have an Oracle Database Server (you called it "back end oracle database") and an Oracle Client (no matter if this is installed on an application server, from Oracle point of view, it is the client)

The version of ODP.NET (Oracle Data Provider for .NET, i.e. the Oracle.DataAccess.dll and some more files) are defined by the Oracle Client. You can use almost every ODP.NET version to connect to every Oracle database version - more or less.

The error message "The provider is not compatible with the version of Oracle client" could also mean there is no ODP.NET provider installed at all. In this case the error message is indeed a bit misleading. So, first check if ODP.NET is installed at all, it is not included in standard Oracle Instant Client installation.

When I check all available downloads from Oracle you have ODP.NET version

  • 9.something
  • 10.something
  • 1.x
  • 2.0
  • 4.0

9.? and 10.? refers to version of Oracle, 1.x, 2.0 and 4.0 refers to version of Microsoft .NET Framwork (strange numbering, but that's how it is). Version 9.? and 10.? are very old, I don't think it makes any sense to use them. 1.x was supported until Oracle client version 11.1.

  • If your Oracle client is 11.2 or higher you are forced to include ODP.NET version 2.0 or 4.0.
  • If your Oracle client is between 10.2 and 11.1 you can use eiter ODP.NET version 1.x or 2.0
  • If your Oracle client is older than 10.2, I don't know how it works - it's outdated anyway.

Version 1.x and 2.0 are not compatible to each other, i.e. perhpaps you have to provide two different setup files of your application to your customer and the customer has to select the correct one depending on his local oracle client installation.

I don't know the situation for 2.0 vs. 4.0, I never used 4.0 so far.

It is not required to put a local copy of Oralce.DataAccess.dll into your application directory. It will be taken from GAC (Global Assembly Cache) where it is installed.

In your developing you only have to take care only these mayor version, for example 2.0. Then your loal GAC knows due to policy files which exact version is loaded, e.g. 2.0.10.2.0.2.20 or 2.0.11.1.0.6.20 or 2.0.11.1.0.7.20 or 2.0.11.2.0.1.2 or whatever.

On top of this all you have to know whether your Oracle client is 32bit or 64bit and include ODP.NET accordingly.

Here you can get more information: Oracle Data Provider for .NET FAQ

Bradley Slavik
  • 875
  • 7
  • 13
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I'm using "oracle.dataaccess.dll" 11g version but why it doesn't work but works when I use 9g version? Isn't is suppose to be backward compatible? – Nil Pun Jan 23 '14 at 21:16
  • You mean the database is version 9i? What is the error message? – Wernfried Domscheit Jan 23 '14 at 22:00
  • I mean "oracle.database.dll" version 9. Error message is incompatible oracle data provider – Nil Pun Jan 23 '14 at 23:10
  • You mean your Oracle Client is version 9? Does it also run still on Windows XP or even Windows 2000? The version of Oracle.DataAccess.dll **has to match always** the installed Oracle Client version. What ist the **exact** version of you Oracle client installation and what is the **exact** version of Oracle.DataAccess.dll? – Wernfried Domscheit Jan 24 '14 at 06:44
  • Dll is definitely 9. How to find version of oracle client installed? – Nil Pun Jan 24 '14 at 08:55
  • Also please help if I need to install ODP.NET and Oracle Client both on server where app is going to hosted? – Nil Pun Jan 24 '14 at 09:00
  • Yes, of course you must install Oracle Client and ODP.NET. Simply start Oracle Setup.exe and enable tickbox for "Oracle Data Provider for .NET" at least. – Wernfried Domscheit Jan 24 '14 at 09:48
  • Really dump question is "Oracle Setup.exe" part of Oracle Client or actual full Oracle Installer. We would like to avoid installing full oracle on client's machine. – Nil Pun Jan 24 '14 at 22:44
  • 1
    Not really strange numbering on the .Net frameworks, given the fact the .Net v3 versions are technically just patches of v2. So v2 and v3 are technically different sub-versions of the same thing, hence why this jumps from v2 to v4. – Nyerguds Dec 30 '21 at 11:24
3

if you have client or oracle instance installed in your system. check the folder name

ORACLE_HOME\product\11.2.0\dbhome_1\ODP.NET\bin\2.x

here you will find the file - Oracle.DataAccess.dll

just include in your reference.

Mohammad shahnawaz
  • 31
  • 1
1

In fact I believe there is no right answer. It all depends on the processor architecture (so bits) the application runs with, the version of OCI client you use, etcetera.

I found it most useful to wrap the Oracle interaction in one class, using Reflection to find the available version to use.

Regarding the version number: the version of the ODP.net assemblies must match those of the OCI client installation. It is a bad idea to mix the 12.* OCI client with the 10.* ODP.net assemblies.

Maybe this article is also useful to you.

Community
  • 1
  • 1
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
  • 1
    Process architecture of application server or oracle database server? – Nil Pun Jan 21 '14 at 21:59
  • 1
    The application in the first place. But if your program says 32-bit you have to install the 32-bit OCI client. If your program says 64-bit you have to install the 64-bit OCI client. If your program says MSIL you never know what you need at compile time, since the actual executing server might be any of those. – Patrick Hofman Jan 21 '14 at 22:13
1

If your Oracle server is version 10.2 or higher, you could consider simply using the managed ODP.NET version which came available with Oracle 12.

Apparently it's one dependency, under 10 MB. It should make deploying your application to different systems a lot easier compared to the ODP.NET versions that depends on Oracle (instant) Client. It should also avoid you having to care about any installed Oracle Client.

However, they do mention it works on "the latest version of the .NET Framework 4.5.1", so from what I understand you need to upgrade your application to 4.5.1, but maybe that is only if you want to use the certain features (like the Entity Framework support).

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

Aaron
  • 105
  • 10
1

Sometimes you get the Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. whereas you have the right dll and the problem is somewhere else.

It happen to me (and this topic helped me a lot to figure it out) and it was the configuration of my application pool in IIS who wouldn't allow 32 bit applications (advanced settings).

SabineA
  • 73
  • 1
  • 1
  • 5