Wednesday, January 20, 2010

Connecting (struggling) to Oracle database

This week I have been working (read struggling) on connecting a C# .NET application to Oracle. In the end I was trapped between 32 bits vs 64 bits, .NET Oracle providers and 32 bit OLE DB providers.

First of all I challenged this task, not bothered by any knowledge about Oracle. I did know that if you want to connect to an Oracle database you have to install the Oracle Client on the client machine. This only 1,7 Gb (!) download (Oracle 11g) happily told me that my Windows 7 version (real version 6.1, 64 bits) was not certified for the job. Only Windows 6.0 was.

Huh, now what?

After some googling found this blogpost about how to trick the setup to install on Window 7 (6.1) anyway.
After that it installed gracefully (some warnings…), time to startup VS to give it a spin!

Well not. That app that I am writing connects besides Oracle and SQL Server to an Access database. Because the Jet OLEDB driver for Access is 32 bits only I am doomed to target the x86 platform for now. No problem, however the 64 bits Oracle client can not connect targeting the x86 platform. Getting a BadImageFormatException:

[InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed.]

(Still having the feeling it is the other way around, because I have the 64 bit Oracle client components installed)

Huh, now what?

Because the ADO.NET provider for Oracle from Microsoft suits well in the other technologies I tried to google more information. As it seems this driver is by the coming of .NET 4.0 deprecated. So on second thoughts not a good choice after all. Using the .NET provider from Oracle (ODP) then? Can I install 32 bits Oracle client on 64 bits machine? Who knows.

Fortunately Twitter came to rescue someone pointed out that there are third party solutions, which can connect to an Oracle database even without the Oracle Client.

So I tried Devarts dotConnect, which indeed can connect without the Oracle Client, isn’t that just great! Within 5 minutes I was connected (either 32 or 64 bits).

So if you want to connect to Oracle on .NET, keep some sparetime afterwards, I would suggest using third party components for it!