Oracle ADO.Net Providers

Having just posted a piece about issues with deploying ODP.Net and Oracle Client with Winforms applications, I thought I'd best also give a quick summary of why, if it's such a pain to use, I've used ODP.Net at all.

Originally we were going to use Microsoft's intrinsic Oracle provider, located in the System.Data namespace of the.Net framework (System.Data.OracleClient). However this has a number of known issue relating to data types, memory leaks, performance and it is in fact now deprecated. Although it is still present in .Net Framework v4, it hasn't been developed in a number of years and will be removed entirely at some point. You can understand why MS don't want to develop it - why encourage people to use a competitor's database platform?

There are a number of commercially available alternatives, the two best know probably being  devArt's dotConnect For Oracle and Progress Software's DataDirect Oracle Data Provider For .Net. In fact, I originally started use dotConnect and it was pretty easy to get it working on the whole (if I can remember much about it I might put a post up as there were one or two things I had to spend some time working out). I was more or less at the testing stage of the proof of concept application I'd constructed, but as is often the case, it came purely and simply down to cost: dotConnect and DataDirect cost money whereas ODP.Net is free from Oracle and most commercial outfits, when confronted with that sort of choice, will inevitably take the freebie.

There are of course pros and cons for each of them and it is up to you (or your organisation) to decide what fits your needs and budget best, but don't be tempted to use the old .Net framework provider as it is a false economy. Hopefully my posts will help people make decisions and then implement them in much less time than it took me. Of course, please feel free to comment with any corrections, additions and the like. I might even make my proof of concept code available to download as well. It is currently in VB.Net but I might do a C# version as well for completeness, but that's a way off as I'm pretty busy.

The provider is not compatible with the version of Oracle client

I've decided to resurrect this blog and try to add more stuff as I find it. This is because these days I am coming up against more and more things which I've had to spend ages trying to track down the solution to, and it would be nice to think I might be able to help someone avoid the levels of sheer anger I've experienced!

Today we are going to talk about Oracle Client (specifically version 11.1) and it's use in Winforms applications. I've actually got a few things to say about this because I have had to start from a base of zero Oracle knowledge and yet make the entire suite of applications my employer produces (total of perhaps a couple of million LOC) work with Oracle when it has been engineered, not especially well, since the year dot to work with SQL Server. This has been a difficult and often highly frustrating project not helped by Oracle's utter intransigence when it comes to following conventions and standards. I will talk about making the Oracle Data Provider for .Net (ODP.Net) and Oracle Client work with systems that use Enterprise Library for data access abstraction; how to deploy a specific version of Oracle Client and ODP with your application to ensure it works no matter what might be installed on a target machine; things to look out for when converting a system to use Oracle and when converting a SQL Server database to Oracle, and possibly other things as I think of them.

However, today I'm going to talk a little about using the Oracle Instant Client, ODP.Net and how to deploy it with your application. I apologise in advance, but this post will be a little out of sequence because this concerns the deployment of an application that has already been developed, rather than the process of developing it. That will come in later posts.

Oracle Client comes in a confusing array of versions and downloadable options. Essentially, there are five main ways to use the Oracle Client:

1. Use the Oracle Universal Installer and install the whole kit and caboodle to a target machine, which is a huge amount of stuff and requires human interaction.
2. Use what is known as the Instant Client installer and install just the client to a target machine. It's still pretty big and of course requires some human interaction.
3. Use the XCopy deployment of Instant Client. You can easily automate this but it's still pretty chunky.
4. Use the XCopy deployment of the Instant Client Light. Getting there...
5. Use only the libraries you actually need and forget about the rest of the crap that Oracle tries to punt.

Since my goal was to make an existing application (a commercially saleable one: Always bear in mind that stuff written for internal and personal use can get away with being slacker and less polished although I would suggest you get in the habit of always making apps as good as they reasonably can be, within reason, whoever the target audience is. It saves you a ,lot of effort in the long run), we are going to discuss how to make an application work with ODP and Oracle Client in the simplest possible way from the point of view of development and deployment, so we are going to use option 5 from that list. 

There are good reasons for choosing the other options in other circumstances. For instance, you might want to work with all sorts of systems that use a particular version of Oracle and therefore you might decide that as a corporate standard every machine should have a particular version of the full Oracle Client installed and make it part of your standard OS build. That is in fact the very situation I faced as some the client for whom this work was originally done used Oracle Client 10.2 as a standard on their workstations. However, we had to consider that other clients would use other versions of Oracle and since Oracle Client and ODP are very fussy about version numbers we had to consider very carefully what version we wanted to use.

The rule of thumb is that Oracle guarantee that any given Client version will work with any database version within two major version either side. Thus Oracle Client 11.1 will work with Oracle 10.1, 10.2, 11.1, 11.2 and the next major version that comes out, whatever that may be. that's why we chose 11.1, but you should consider you current and future requirements carefully and choose appropriately. In another post I will talk about how to ensure that your application uses the version Oracle Client that you want it to, not the one Oracle thinks you should use.

With regards to the pickiness of Oracle Client and ODP, what I mean is this: You cannot mix and match ANY part of the Client and ODP binaries across ANY version numbers. Always but always make sure you get all the binaries you need from one single Oracle Client download or it simply will not work. In my case (and yours if you simply wnat to get your application to talk to Oracle) the list of binaries required is as follows:

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

Note that oraociei11.dll is pretty huge - just shy of 115MB - because it is, I believe, a fully multilingual component. There is a smaller one you can use from the 'light' version of the instant client called oraociicus11.dll which is something like 33MB, but again make sure you get all the components from the same download. If you want the smaller, English-only oraociicus11.dll then download the 'Light' version of the Instant Client XCopy deployment package and get all the binaries from that, otherwise get the XCopy deployment of the full Instant Client and get your binaries from that.

Having done my development and got my build process to work (we use TeamCity continuous integration based on Wix and MSBuild to produce our installers), I wanted to do some unit testing on my work before sending it off to our testing team because I don't want to waste their time with problems I can find and fix before they get it. To do this I use a clean Win7 virtual machine so that none of my development stuff can influence the working of the application. As an aside, NEVER do any functional testing of your stuff on your development machine because there is nothing more annoying than hearing a dev say "It works on my machine" when presented with a bug that has occurred on a machine that doesn't have a million and one things installed on it.

Once installed on my VM, I fired it up and...got an exception, so I looked in the Windows Application Event Log (where we log our exceptions. You are handling and logging your exceptions, right?) and found that I was getting an error which says: 

'The provider is not compatible with the version of Oracle client'

That's odd. I know for sure that I got all my binaries from one Oracle Client download, so what's going on? I checked, re-checked and then checked again that I had the right versions, that I wasn't missing anything and so on. Finally, after much digging and Googling I realised that my VM did not have the Visual C++ 2003 redistributable on it. Of course, my development machine does so it doesn't go wrong at all, but without the file msvcr71.dll Oracle Client will not work. This is because some of the Client components have been compiled against this version of VC++ and therefore naturally they need this in order to work properly. You won't get this error if you used the Oracle installer to install the Client but of course we don't want to do that.

Microsoft recommends that if you need to add this file then you add it into your program directory and NOT into Windows\system32, so make sure that you include the msvcr71.dll binary in your library of dependencies and that your build process includes it. It will do no harm to have it in your program directory even if it has already been installed on the target machine.

Try it on a machine you can break with impunity (I keep one master 'clean' Win 7 VM and simply clone it as needed). Get your app installed and see if you have msvcr71.dll on the machine. Remove it if you do and you'll see the app fail with the error I've described. Add it back in (you kept a copy, yes?) and see it suddenly start working. This cost me nearly 3 days of frustration until I stumbled upon this blog post and a comment, about half way down, from a guy simply called Guilherme which fired a neuron in the back of my brain which reminded me that I'd had this file missing from a VM before and it caused problems.

I may edit this post at some point once I've re-read it to make sure it makes sense and I'll add new posts soon(ish) talking about the other challenges I've had to overcome to get our systems to use Oracle properly.