Wednesday, June 13, 2007


Using LLBLGen Pro as your Data Access solution for an Oracle Database, you either have to use Microsoft's Provider For Oracle, or Oracle Data Provider for .NET (aka ODP.NET).

And both of them work on top of Oracle Client. So an Oracle Client is needed for any of the above providers, which is installed automatically when you install the ODP.NET.

Many .NET developers favors the ODP.NET over the MS provider, especially those with a long track dealing with Oracle database. Because the MS provider has some limitations (lacking the support of some Oracle Native DataTypes, eg. XMLType).

Using LLBLGen Pro with the ODP.NET:
When you build the code generated by LLBLGen Pro on the Development machine, it references the Oracle.DataAccess.dll version found on your machine.
(Oracle.DataAccess.dll is the Oracle Data Provider assembly)

Also it's worth noting that the "SD.LLBLGen.Pro.DQE.OracleX.NET20.dll" the Dynamic Query Engine shipped with LLBLGen Pro, and referenced in the generated code, was built against a specific version of the Oracle.DataAccess.dll (eg. SD.LLBLGen.Pro.DQE.Oracle10g.NET20.dll was built against Oracle.DataAccess.dll v. as far as I can remember).

This shouldn't cause any problems in most of the cases, since installing the ODP.NET installs some Publisher Policy files that redirect calls to older versions of the Oracle.DataAccess.dll to the newer installed version.
I said most of the cases because some older versions of the ODP.NET missed those publisher policy files. If this is the case you may include the assembly redirections into your app.config /web.config file, which should look like the following:

[assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"]
[assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89B483F429C47342"/]
[bindingRedirect oldVersion="" newVersion=""/]
(Replace the square brackets with the triangular ones used for xml.)

Note: Assembly redirection can work the other way around, you can re-direct calls to a new version of the Oracel.DataAccess to an older installed version.

ODP.NET Versioning Schema:
It's worth mentioning here that Oracle have changed the ODP.NET / Oracle.DataAccess versioning schema.
Starting with, Oracle Data Provider for .NET ships with two sets of binaries; one set for .NET Framework 1.x and another for .NET Framework 2.0.

For example, if the ODP.NET product version number is, the correspondingODP.NET assembly versions are:
■ .NET Framework 1.x version:
■ .NET Framework 2.0 version:

Note that the Oracle installer and documentation still refer to the ODP.NET product version number and not the assembly/DLL version number. As with the .NET Framework system libraries, the first digit of the assembly version number indicates the version of the .NET Framework to use with an ODP.NET assembly. Publisher Policy DLL is provided as before so that applications built with older version of ODP.NET are redirected to the newer ODP.NET assembly.

The Problems:
Problems can rise when you deploy your application on a machine with a different version of the ODP.NET, then you might see the following exception:
Could not load file or assembly 'Oracle.DataAccess, Version=x.x.x.x, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.

This should be solved by a correct assembly redirection. (From the version you see in the exception to the version already installed).

Another exception you might see is:
The Provider is not compatible with the version of the Oracle Client.

An assembly re-direction should solve this issue, too. This shows up when you have different version of the Oracle.DataAccess.dll in your machine check the Global Assembly Cash (GAC).
Due to older installations or so, while only one of them is corresponding to the latest installed version of the ODP.NET, yet your application references an older one. The first exception won't show up, since your application can find the dll, thanks to the GAC, but it's not the one that should be used.

Just to make sure which ODP.NET version is installed on your machine, check the following registry path HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET

In most of the cases the assembly re-direction are the answers.

In some rare cases after getting rid of the re-directions issue you might see the following exception:
Unable to cast object of type 'Oracle.DataAccess.Client.OracleConnection' to type 'Oracle.DataAccess.Client.OracleConnection'

Pretty weird!!
This can be caused by some calls to the Oracle.DataAccess.dll inside the SD.LLBLGen.Pro.DQE.OracleX.NET20.dll which were not successfully re-directed to the installed version.
To solve this issue you will have to re-build the DQE dll to reference the same version of the Oracle.DataAccess.dll found on your development machine.
Details can be found in this post:


Ed Hammerbeck said...

Hi, I stumbled across your blog while trying to diagnose my ODP/GAC worries. I go into detail in this thread on OTN. The error I am getting is the one you describe: "System.TypeInitializationException: The type initializer for "Oracle.DataAccess.Client.OracleConnection" threw an exception. The provider is not compatible with the version of Oracle client." However, I can see that in the GAC that the version numbers are all the same [].

If you can give me some advice, I would be very grateful.

Anonymous said...

Thanks man , for this article !