Friday, July 12, 2013

Managing Oracle Clients for .Net Apps

This is more of a self reference note and a bit of victory celebration as well. After several attempts spanning a couple of years, I feel I have come in to grips with managing oracle clients in a windows environment.

For MSSql developer (like I was 3 years back), will have no idea how frustrated a .Net developer could be trying to manage drivers/client requirements with an Oracle DB. In addition to the natively complicated way Oracle goes about doing things, the convoluted state of the Oracle drivers for .Net didn’t help. At that time there were 2 commonly available client drivers, one by Microsoft and one by Oracle. Microsoft packaged there driver along with the .Net framework so many end up using that one to realise later that in a production environment you better have ‘Oracles’ Oracle driver. Also they were named quite ambiguously, System.Data.OracleClient and Oracle.DataAccess. At last Microsoft decided to deprecate their driver from .Net 4.0, so the choice is quite obvious now. The oracle driver for .Net is now commonly identified as ODP.Net. (Oracle Data Provider for .Net)

ODP.Net comes in different versions mainly targetting major oracle releases like 10g, 11g etc. The documentations around installing and managing these drivers is quite confusing from Oracle. Also in a developer machine there’s a high possibility of coexistence between different versions.  To make it worst, in the environment that I currently work, an outdated oracle client is installed by default to support some legacy enterprise applications. So coexistence is inevitable.

When a development of a new applications comes about we have to choose between continuing with the enterprise approved legacy driver or a more modern and upto date driver where we will need to wrestle with challenges (both technical and bureaucratic) posed by rolling out a new driver within the enterprise. Of course we choose the later and went with Odp.Net 11.2.

So as developers we had to do several things

1. Install the oracle client (Something like 11.2) from oracle in both dev and server environments

2. Make sure the project references Oracle client instead of Microsoft client - both at compile time and at runtime

3. Make sure our application resolves to the new oracle client

This is pretty much what we needed to get a Web Application up and running in a server that we manage. However getting client applications (e.g WPF application) rolled out posed a far superior challenge as I’ll explain later.

Installing Oracle Client

This is not an exhaustive step by step instruction set, but rather some of the minor points that we as developers tend to step over and ignore until we realised that we’ve wasted a lot of time cursing Oracle.

  • There are 2 editions for 32 bit and 64 bit. You may want 32 bit for developer machines, but most of your servers may be in 64 bit
  • In either edition you got one packaged with Oracle Dev Tools and a slim version with just the client. I prefer the slim version. (Specially since I need to target servers later in the installation process)
  • After you unzip the package - Read the readme file carefully. I found it useful in several occasions.
  • Make sure you have full admin rights to the box. Without admin rights, the installation will look like a success while it’s not. (See verifying the installation - last bullet point)
  • Ready to restart the server once done. (This can be problematic in a production environment, where a reboot needs to cut through a lot of red tape)
  • Make sure you name the installation folder appropriately, so it can be distinctly identified in case of multiple Oracle client installations
  • Verifying the installation
    • Use a tool like Oracle Locator Express to check whether the new oracle client turnes up
    • Check the installation folder for availability of all components installed (like folder for each .net framework under folder)
    • Check the machine.config for an entry as follows;
<section name="oracle.dataaccess.client" type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
(I found out that in the case of not having full admin rights, this step is not completed while the 2 before were successful)

Reference correct oracle client from the project (Compile Time)

Once the installation is complete, it’s easy to reference the client. The assembly of interest is Oracle.DataAccess.dll. Generally this can be kept as a dependency (copied) in the current project directory by copying it from the Odp.Net folder of the client installation. Make sure you copy the version matching the .Net framework in use.

Making sure that the application finds the new Oracle Client (Run Time)

In a runtime environment with just one Oracle client installation this is quite easy. You just have to make sure that the environment variable ORACLE_HOME is pointing to the correct folder. (Again you can use Oracle Locator Express to find out oracle home for a given client installation)

However, in an environment where you may have multiple oracle clients, this can be tricky. To isolate your app from any other client installation you can set ORACLE_HOME for the application process as follows. (This piece of code may be invoked at the start of the application before anything to do with Oracle DB)

The good thing about this approach is that it’s not changing the environment variables for any other application running in the same machine.

Above steps are sufficient to get an application (Web or Windows) running on a machine having an Oracle client installation. However there are instances where installing the client on every single machine wanting to run the application is not possible or productive. A self-sustained windows forms or Silveright app in an Enterprise is a classic example.In these instances one can resort to installing the client in a centralized location and enabling the client apps to resolve the client from that central location.


A different type of error was encountered when installing oracle client in Windows Server 2008 r2 box. Ths issue was caused by a permission issue to $ORACLE_HOME folder, applications were not able to connect to the driver. This was fixed by adding the group 'Authenticated Users' with Read & Execute permissions to the $ORACLE_HOME folder. For details refer this link

Another potential error that can occur is having any residual (or previous) Oracle installation directories in your PATH variable. Make sure the current installation directory is mentioned before any other installation director in the PATH. If your installation directory is c:\oracle the path should have c:\oracle and c:\oracle\bin
Post a Comment