This was a big deal for me last week. Oracle on Windows is not the friendliest of environments, and you don’t always have the choice of where and what your database is going to be. For a lot of smaller, line-of-business application, SQLExpress is the way to go. But sometimes, you’re dealing with things that are big. And when things are big, the Enterprise has a say.

So here’s the situation: I am updating a WCF web service that communicates between two servers. In the time since the original code was written and the time for updates was needed, MS went and marked the entire obsolete. So we’re going to remove that library and use the the new Oracle Data Access Client from Oracle’s web site. My development machine is Windows 7 x64. I will be deploying to Windows Server 2003 x86. I’m using NHibernate to communicate to the database, so there are some session factory configuration changes that I’ll need to make.

1. On your development machine, download and install the Oracle Developer Tools for Visual Studio. You’ll need to set up your Oracle home directory. I made mine C:\Oracle\OraHome11g_ODAC. From now on, I’ll refer to this folder as ORACLE_HOME. Add ORACLE_HOME and ORACLE_HOME\bin to your network path. As far as I can tell, it doesn’t really matter where you put this, other than you’ll want to know it. Make sure that you set both your tnsnames.ora file in your ORACLE_HOME\Network\Admin folder,
2. Restart your development machine. You should be able to use SQLPlus to connect to a database and run a few select statements to make sure that everything is working.
3. Remove any references to System.Data.OracleClient. As already mentioned, Microsoft marked that entire library obsolete. Instead, add a reference to Oracle.DataAccess. This should be coming out of your GAC. As of this writing, the version is 2.112.2.0. On my machine, any time I added this library, it set the Build Setting to Copy Local = true. Set that to false. It ended up causing me a lot of problems.
4. In your web.config file, you’ll need to add a setting to instruct Oracle to come from the GAC. That should look like this:

   
   <configuration>      
   
     <runtime>
   
       <assemblyBinding>
   
         <qualifyAssembly partialName="Oracle.DataAccess" fullName="Oracle.DataAccess,Version=2.112.2.0,Culture=neutral,PublicKeyToken=89b483f429c47342" />
   
       </assemblyBinding>
   
     </runtime>
   
   </configuration>
   
       

   Why do I need this step? The answer: You may not. When you install the Oracle tools, the installer put the AMD64 assembly in my GAC. This makes sense because I have a 64-bit machine. As previously stated, I am deploying to a 32-bit machine. If you’re deploying from 64-bit to 64-bit, you can probably leave Copy Local = true and forget this part of the instructions. Also, this doesn’t always have to be a server issue. Even if you were coding on your awesome development machine and publishing to a 32-bit client, you’d need this setting in your app.config.
5. Update your NHibernate session factory to use the NHibernate.Driver.OracleDataClientDriver (or OracleDataClientConfiguration, if you’re using FluentNHibernate). Do not use the OracleClientConfiguration. That is meant to work with the old Microsoft library.
6. Make sure that the application works on your development machine. This should be obvious, but it isn’t always. I’ve seen developers push non-working code to a server, only to hope that it works when it gets there. It’s sad, but I need to remind people about this step.
7. Install the appropriate version of the Oracle Data Access Client on your server. Do the same thing with adding the values to the environment path. Restart the server. Test a connection with SQLPlus.
8. After all of that, you should be able to deploy your application. (At least I was.)

I stuggled with this for about 6 hours on Thursday last week. I tried the qualifyAssembly thing, because I found that answer somewhere. But that didn’t work because Copy Local = true was still set. I guess it doesn’t matter what’s sitting in your GAC if there’s a copy of the DLL in your bin folder. At first, I thought that this was all quite the pain to get up and running. However, by Friday afternoon, everything was just working wonderfully. And that’s something I never thought would happen with Oracle.

Happy coding!