Oracle Instant Client and Entity Framework trouble with configuration

Question

I'm trying to learn and figure out if it is possible to deploy an MVC, EF, ODAC 11.2.0.3 app to a server that has a previous version of ODP.NET installed. Rather than updating the sever ODP.NET (which I can't), I figured I could use the Oracle Instant Client.

Is this doable?

1) I added these dlls to my project to support Instant Client

-Oracle.DataAccess.dll

-oci.dll

-ociw32.dll

-orannzsbb11.dll

-oraociei11.dll

-OraOps11w.dll

2) Next I updated web.config for the dbProviderFactories

   <system.data>
    <DbProviderFactories>
    <add name="Oracle Data Provider for .NET"
    invariant="Oracle.DataAccess.Client"
    description="Oracle Data Provider for .NET"
    type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess,    Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
    </system.data>

3) This (afaik) is how to use the Oracle dll in the bin rathre than the GAC

<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
  <dependentAssembly>
    <assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89b483f429c47342" />
    <publisherPolicy apply="no" />
  </dependentAssembly>
</assemblyBinding>
</runtime>

4) Finally my connectionString

    <connectionStrings>
    <add name="Entities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=Oracle.DataAccess.Client;
provider connection string=&quot;DATA SOURCE=XXX;PASSWORD=XXX;PERSIST SECURITY INFO=True;USER ID=XXX&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

This is the error I receive Unable to find the requested .Net Framework Data Provider. It may not be installed.

I really appreciate any help here. I'm rather new and have a lot to learn. Thanks in advance. cheers

Solution 1

Add a <remove … /> section in the <DbProviderFactories> element in the web config to remove any existing Oracle provider. (before the <add>)

<remove invariant ="Oracle.DataAccess.Client" />

Solution 2

Here is my Xcopy solution.

I posted it over at

(https://jeremybranham.wordpress.com/2011/04/25/oracle-instant-client-with-odp-net/#comment-181)

as well.

But I think I can post my xml without formatting issues here.

Nuget "packages.config"

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="CommonServiceLocator" version="1.0" targetFramework="net35" />
  <package id="EnterpriseLibrary.Common" version="5.0.505.0" targetFramework="net35" />
  <package id="EnterpriseLibrary.Data" version="5.0.505.0" targetFramework="net35" />
  <package id="EntLibContrib.Data.OdpNet" version="5.0.505.0" targetFramework="net35" />
  <package id="Unity" version="2.1.505.2" targetFramework="net35" />
  <package id="Unity.Interception" version="2.1.505.2" targetFramework="net35" />
</packages>

app.config

(note the <clear /> tag below. this may or may not be needed, but I figured it was better to clear them out since you don't know what may be in the machine.config file)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" />
  </configSections>


  <dataConfiguration defaultDatabase="OracleMainConnectionString">
  </dataConfiguration>

  <connectionStrings>

    <add name="OracleMainConnectionString"
         connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyOracleServerName)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyServiceName)));User ID=MyUserName;Password=MyPassword;"
      providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

  <appSettings>
    <add key="ExampleKey" value="ExampleValue" />
  </appSettings>

  <system.data>
    <DbProviderFactories>
      <clear />
      <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description=".Net Framework Data Provider for Oracle" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
      <add name="EntLibContrib.Data.OdpNet" invariant="EntLibContrib.Data.OdpNet" description="EntLibContrib Data OdpNet Provider" type="EntLibContrib.Data.OdpNet.OracleDatabase, EntLibContrib.Data.OdpNet, Version=5.0.505.0, Culture=neutral, PublicKeyToken=null" />
    </DbProviderFactories>
  </system.data>


</configuration>

I am developing on a x64 Windows 7 machine.

I downloaded: ODAC1120320Xcopy_32bit.zip (from oracle.com)

Which is the:

ODAC 11.2 Release 5 (11.2.0.3.20) Download the XCopy version [Released September 11, 2012]

I unzipped this zip file.

I searched and fished out these files:

oci.dll Oracle.DataAccess.dll orannzsbb11.dll oraociei11.dll OraOps11w.dll

Note, when there were 2 files of the same name, I took the "bin\2.x\" or "odp.net20\bin" version for my 3.5 Framework need (I'm not on 4.0 yet).

I took these files, and put them in a subfolder from where my .sln file resides.

.\MySolution.sln
.\MyConsoleApplicationFolder\MyConsoleApp.csproj
.\ThirdPartyReferences\
.\ThirdPartyReferences\Oracle\

I place all the files above in the

.\ThirdPartyReferences\Oracle\ 

folder

I used "Add Reference" to add a reference to Oracle.DataAccess.dll to the "MyConsoleApp.csproj" csharp project. (This meant browsing to "..\ThirdPartyReferences\Oracle\" of course)

I used a "Post Build Event" to copy the "extra" (aka, "accessory)" files

My lines in my post build event were:

copy $(ProjectDir)..\ThirdPartyReferences\Oracle\oci.dll $(TargetDir)*.*
copy $(ProjectDir)..\ThirdPartyReferences\Oracle\orannzsbb11.dll $(TargetDir)*.*
copy $(ProjectDir)..\ThirdPartyReferences\Oracle\oraociei11.dll $(TargetDir)*.*
copy $(ProjectDir)..\ThirdPartyReferences\Oracle\OraOps11w.dll $(TargetDir)*.*

Note, my post build event replaces the "Copy if Newer" from the URL instructions above.

When I ran my project........I got a few missing dll errors.

Note: In the assembly that has your calls to the EnterpriseLibrary.Data objects…you’ll get “Cannot find Microsoft.Practices.SomethingSomething namespace. Just keep adding references to these dll’s (that the above package.config will pull down) until the errors go away.

Like here is a specific one:

"Could not load file or assembly 'Microsoft.Practices.ServiceLocation, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified."

So (after running Nuget of course, to download all the files) I went and added a reference to:

\packages\CommonServiceLocator.1.0\lib\NET35\Microsoft.Practices.ServiceLocation.dll

That cleared up the issues.

And my csharp code: (note "select *" is for demo purposes only)

/*
    using System;
    using System.Data;
    using System.Data.Common;
    using Microsoft.Practices.EnterpriseLibrary.Data;
 */
public IDataReader EmployeesGetAll()
{

    IDataReader returnReader = null;

    try
    {

        Database db = DatabaseFactory.CreateDatabase();
        DbCommand dbc = db.GetSqlStringCommand("SELECT * FROM ( SELECT * FROM TEMPLOYEE ) WHERE ROWNUM <= 25");
        returnReader = db.ExecuteReader(dbc);
        return returnReader;

    }

    finally
    {
    }

}

And it worked.

Thank you:

https://jeremybranham.wordpress.com/2011/04/25/oracle-instant-client-with-odp-net/#comment-181

I think this makes ODP.NET an "xcopy" deployment.

I still need to test on a clean machine to be sure.

But its the end of the day..............

================

Additional Information:

Everything above is correct. However, I hit a caveat. I was using a "Console Application" to test my code.

When you add a new Console Application to visual studio, it DEFAULTS to x86.

As seen here:

http://www.xavierdecoster.com/post/2011/02/15/console-application-visual-studio-gotcha-on-x64-os-aspx

EDIT: (Updated link)

http://www.xavierdecoster.com/post/2011/02/15/console-application-visual-studio-gotcha-on-x64-os

So when I put all the configuration and code and stuff in a real project (which was set to "Any CPU" on a x64 bit machine)...everything I had done stopped working. :<

After tweaking a bit........ I found this file on oracle.com ODAC1120320Xcopy_x64.zip I then repeated everything I did above , but searching the unzipped files of this x64 zip file.

Everything is working.

But that "x86" default thing with a Console application threw me for a loop.

Solution 3

It seems from your question that you need to deploy an update to your application and the new version of ODP.net using only xcopy deployment permission.

Since your application is being changed, then you shouldn't need the assembly binding changes or DbProviderFactories. Just update the csproj of the class library with your edmx etc to have a reference to the new ODP.net version, eg

<Reference Include="Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342, processorArchitecture=x86" />

If you get an issue with your tnsnames.ora, then you would have to do one of the following: a) Add a system environment variable TNS_ADMIN to point to the directory of the tnsnames.ora, or b) Change the connection string to something based on:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

c) See if you can put a copy of the tnsnames.ora somewhere else.

Comments

[Mustang31]

Hi Arieh, I think I wasn't clear with my question. It's a new webapp that uses Oracle Instant Client with the latest ODAC from Oracle with EF support. I want to deploy this to a server that doesn't have the latest odp.net installed

[Mustang31]

That did the trick Jeremy. I think I also had the wrong dlls as Christian pointed out. thanks again everyone for your help!

[granadaCoder]

1. I put my files from my \bin\Debug\ directory onto a semi-clean machine and my console application worked. That's the good news.

[granadaCoder]

2. The pooper with this solution. oraociei11.dll is 127 MB ! Youch!

[Kiquenet]

xavierdecoster.com/post/2011/02/15/… not found

[granadaCoder]

Here is an updated URL for that broken link : xavierdecoster.com/post/2011/02/15/…

[Taha Rehman Siddiqui]

This solved my problem as well on a windows server 2012. Thank you.

[thomasb]

For anyone running into this because they get a ConfigurationException about InvariantName on Oracle.DataAccess.Client : edit your machine.config to remove duplicates.