Oracle Instant Client with ODP.NET

Posted: April 25, 2011 in .NET
Tags: , , , ,

Connecting to an Oracle database without installing an Oracle client software package on the server/client deployment environment:

Some application deployments to clients or servers require database connections to Oracle instances. What happens when the deployment environment doesn’t have the Oracle client software loaded? What if the server configurations are not accessible, or it takes an act of congress to install anything to the environment?

In this example I am adding  ‘instant client’ support to a .net application using the ODP.NET driver but other drivers can leverage the instant client. Also, I am using the latest ODP.NET driver for the .NET 4 framework, as it supports the Entity framework =)

Required software -

  • Oracle Instant Client
  • Oracle ODP.NET (or your preferred Oracles driver that is supported by Instant Client)

http://www.oracle.com/technetwork/indexes/downloads/index.html

In your .net application, add the following 5 DLLs (from the Oracle download) to the root directory of your project and configure the properties of each to ‘Copy if newer’.

  • ODP.NET Drivers
    • OraOps11w.dll
    • Oracle.DataAccess.dll (if this is in your ref folder, set the property to ‘Copy Locally’
  • Instant Client Files (Depending on the version you downloaded, you only need one set)
    • Basic
      • oci.dll
      • orannzsbb11.dll
      • oraociei11.dll (111M)
    • Light
      • oci.dll
      • orannzsbb11.dll
      • oraociicus11.dll (~31M)

 If you are using Entities or need access to the factory in ODP.NET, you’ll need to register the provider in the web.config (*check the version of your driver to be sure it matches) -

<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.2.30, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

 When you build/deploy your application, the 5 DLLs should be in the web app bin folder, or if you are building a client app - they should be in the same folder as the exe.

If you want to use the TNS-Less connection string format for entities, the Entity connection string may look something like this in the web config -
<add name="PSDEntities" connectionString="metadata=res://*/Data.PSD.csdl|res://*/Data.PSD.ssdl|res://*/Data.PSD.msl;provider=Oracle.DataAccess.Client;provider connection string=&quot;DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBService)));PASSWORD=****;USER ID=****&quot;"
      providerName="System.Data.EntityClient" />

*notice the &quot; before and after the embedded connection string.

 

References -

Good tutorial with screen shots (http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html)

Oracle's Developer Guide (http://download.oracle.com/docs/html/E10927_01/InstallODP.htm)

About these ads
Comments
  1. Timothy Little says:

    Jeremy, this is some good info that I know I will put to pratice.

  2. [...] Oracle Instant Client with ODP.NET (jeremybranham.wordpress.com) [...]

  3. Adam says:

    This did not work for me. Strangely, I’ve successfully used these steps in other projects without the entity framework but in this case, I wanted to use it. Instantiation of my entity context fails:

    FRTREntityContext context = new FRTREntityContext();

    Stack Trace:

    [OracleException (0x80004005): The provider is not compatible with the version of Oracle client]
    Oracle.DataAccess.Client.OracleInit.Initialize() +356
    Oracle.DataAccess.Client.OracleClientFactory..cctor() +50

    [TypeInitializationException: The type initializer for 'Oracle.DataAccess.Client.OracleClientFactory' threw an exception.]

  4. Adam says:

    well, i made some minor changes in the web.config that must have restarted the application pool and it appears to be working now. Thanks for your post, the more people that document this method clearly the less time it will take to look it up next time :-)

  5. Doug Clutter says:

    Thanks for sharing this. The bit about adding the factory to config was priceless. Every other resource on the web want you to modify the machine.config. Again, thanks!

  6. Joel Diaz says:

    Are you using the tnsnames.ora file? If so where did you place it on the server? Thanks.

  7. Steve says:

    Thanks for the information Jeremy. I’m trying to get this working but running into some snags.
    If you got a moment can you check out my post on stack overflow?
    http://stackoverflow.com/questions/9911948/oracle-instant-client-and-entity-framework-trouble-with-configuration

    I’m using your posted information above but thus far it seems my configuration is incorrect.

  8. Wes says:

    Glad I stumbled on this. I am trying to connect to Oracle using the Entity Framework in a project right now, but I have to use a TNS-Less connection string similar to your example. I thought maybe I could just create my Entity Data Model and then replace the connection string in the config file, but I can’t manage to get past the wizard for creating the data model. I select the oracle provider, but then the window that asks for the server name and credentials is the next step. I don’t have a way to configure it the way it needs to be, so it will not let me connect or create the .edmx. Have you been able to get around that? How? Thanks.

    • Can you access the ‘advanced’ button during the connection process to change the data source to include the tns data?

      • Wes says:

        Ah, excellent! I looked through the advanced tab before, but didn’t realize I could put all of that into that property. Anyways, I inserted my connection string into that property and it worked perfectly once I got the filters set up for the schema I have access to. Anyways, thanks for pointing me in the right direction :)

  9. Sathiya.lr@gmail.com says:

    Excellent Jeremy. It is working perfectly. There is no words to express how happy i am right now. Because installing a software in our production server has lot of formalities. But now i am concern about how i am going to move 111Mb file? :-)

  10. sandy says:

    Thank you very much…it worked for me

  11. Yang says:

    thanks it is work for .Net to Oracle db connectivity perfectly. otherwise It might be install the Oracle client installer.

  12. [...] Reference: Connecting to Oracle from C# / Winforms / Asp.net without tnsnames.ora Instant Oracle Using C# Packaging Oracle Data Access Components into .Net projects What is the minimal setup required to deploy a .NET application with Oracle client 11? Oracle Instant Client with ODP.NET [...]

  13. Jamil says:

    Great, thanks! this information save me from a big change. FYI, all these dlls you can find in the x-copy setup of ODP.NET.

  14. granadaCoder says:

    BLESS YOU!

    I’ll describe how I got mine to work (using your instructions).

    I downloaded:
    ODAC1120320Xcopy_32bit.zip

    I unzipped this zip file.

    I searched the unzipped files…for the files you mention above.

    I am also using the EntLibContrib Data OdpNet Provider.

    Here is my config file that worked!

    (Note the “Oracle.DataAccess, Version=2.112.3.0″ seen below, which matches the zip file from above (the version of Oracle.DataAccess.dll that is)

    And here was my NuGet packages.config information.

    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.

    PS
    You can also use a “Post Build Event” to copy the needed files:

    Now, I had a reference to Oracle.DataAccess.dll in one of my csharp projects.
    So that one was coming over from the added reference.

    For the “extras”, I used a post build event (on my presentation layer).

    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: “..\ThirdPartyReferences\Oracle” is a relative to where I copied my files.
    It is my own naming convention. Yours may vary.

    The Post Build Event is an alternative to the “‘Copy if newer’” instructions above.

  15. granadaCoder says:

    WordPress is deleting my xml (config) code.

    Here it is again. Replace GTSIGN with the appropriate xml tag. and LTSIGN with the (other) xml tag.

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

    LTSIGN dataConfiguration defaultDatabase=”OracleMainConnectionString” GTSIGN

    LTSIGN /dataConfiguration GTSIGN

    LTSIGN connectionStrings GTSIGN

    LTSIGN 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” / GTSIGN
    LTSIGN /connectionStrings GTSIGN

    LTSIGN appSettings GTSIGN
    LTSIGN add key=”ExampleKey” value=”ExampleValue” / GTSIGN
    LTSIGN /appSettings GTSIGN

    LTSIGN system.data GTSIGN
    LTSIGN DbProviderFactories GTSIGN
    LTSIGN clear / GTSIGN

    LTSIGN 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″ / GTSIGN

    LTSIGN 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” / GTSIGN

    LTSIGN /DbProviderFactories GTSIGN
    LTSIGN /system.data GTSIGN

    LTSIGN /configuration GTSIGN

  16. granadaCoder says:

    Here is my nuget package.config (using same xml replacement trick from above)

    LTSIGN ?xml version=”1.0″ encoding=”utf-8″? GTSIGN
    LTSIGN packages GTSIGN
    LTSIGN package id=”CommonServiceLocator” version=”1.0″ targetFramework=”net35″ / GTSIGN
    LTSIGN package id=”EnterpriseLibrary.Common” version=”5.0.505.0″ targetFramework=”net35″ / GTSIGN
    LTSIGN package id=”EnterpriseLibrary.Data” version=”5.0.505.0″ targetFramework=”net35″ / GTSIGN
    LTSIGN package id=”EntLibContrib.Data.OdpNet” version=”5.0.505.0″ targetFramework=”net35″ / GTSIGN
    LTSIGN package id=”Unity” version=”2.1.505.2″ targetFramework=”net35″ / GTSIGN
    LTSIGN package id=”Unity.Interception” version=”2.1.505.2″ targetFramework=”net35″ / GTSIGN
    LTSIGN /packages GTSIGN

  17. granadaCoder says:

    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

    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.

  18. toregua says:

    Very nice article! Thank you very much, It works well for me :)

  19. sanksuri says:

    Thanks!, it works for me.

  20. Alvaro says:

    Gracias por la ayuda, busque por todo lado tu solucion me funciono muy bien.

    Gracias

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s