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!

6 comments:

Dave said...

Oracle is fun, ain't it? :-)

FWIW, it is possible to install the 32 bit Oracle client on a 64 bit OS. It is even possible to have both versions installed side-by-side.

Roland Beenhakker said...

@Mike Thanks for the info! As a Oracle rookie that is handy.
@Dave good to know that 32 bit client and 64 bit client can be installed side-by-side, will give that a try.

Anonymous said...

Of course you can't use a 64 bit client from a 32 bit app. Win64 does not allow 32->64 bit thunking and viceversa.
Also I do not know what client you downloaded - the client-only installer is about 500MB, you've got the whole database setup - and which release you got? 11.1.0.6 or 11.1.0.7? Check Oracle/Windows compatibility matrix, or you can hit nasty surprises.
I'd suggest you to download Oracle 11g ODAC 11.1.0.7.20 with Oracle Developer Tools for Visual Studio

Anonymous said...

You can also use Oracle's Instant Client for a simple deployment. Also DevArt is not Oracle supported :)
Anyway any company without "Oracle support stuff" is doomed. Oracle is a beast that requires daily sacrifices to deliver its wisdom.
For example, is the database running in archivelog mode? I hope so, or you will lose media failure recovery. But archivelog needs archivelog management on separete disks - and scheduled backups via RMAN. Good luck...

Unknown said...

You made the right decision. DevArt has the best .NET Data Providers for non Microsoft DBMSes on the market today. They beat the stock and vendor providers hands down.

--jeroen

Anonymous said...

please...no odac...he is giving us lot a trouble and now my team is not going to support odac anymore

Use an image as your UIBarButtonItem

Using an image as your UIBarButtonItem in your navigationcontroller bar can only be achieved by using a common UIButton as the BarButtonItem...