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!


Mike said...

Don't give up to early. The original .net stuff is not bad at all and has good debugging support in VS for PL-SQL.

It makes sense to try the instant client, then you only need to set a few environment variables and you are done. Oracle is UNIX. Imagine everything is started with a script where in the first line the oracle home is exported ... in Windows you have the registry keys ... Oracle Home and TNSNAMES directory ... you also find the resulution priority in the TNSNAMES.ORA ...

The only thing that is really somehow bad but understandable. If you have a server installed (11.x.x.x) and use the new the client is updated - a second home is installed, the original OCI.DLL is removed from the old HOME, but the Oracle Home selector lets programs find the old home, with no OCI.dll, first.

We encountered no problems using the 10g Client. It is always good to have a service account to download the patches ...

I like it. So my choice is if possible the devart .net stuff in direct mode too. It's handier and a lot simpler.

On the other Hand OCI is faster in lots of situations and if you have to use the oracle name service ONAMES then the direct connect approach does not work ...

So I see the Oracle installer sealed your faith and welcome.


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? or Check Oracle/Windows compatibility matrix, or you can hit nasty surprises.
I'd suggest you to download Oracle 11g ODAC with Oracle Developer Tools for Visual Studio

Mike said...

@秘密's post

These experts advise direct connect too;-).

It makes sense to install the latest ...

(*I'd suggest you to download Oracle 11g ODAC with Oracle Developer Tools for Visual Studio*)

This is the Datasbase installer.

Please check the "Developer Tools Section" ... here you find the latest "Oracle 11g ODAC".
--> In order to avoid ODAC vs. Client issues it makes sense to take the download in combination with the corresponding client.

Take care of issues with the client update I encountered ...

-->If you want the developer tools too .... with Tools for developer Studio. The moment Oracle is commited you get a lot from them "onboard".

Devart is ok ... imho it is a more painless way in the beginning. The moment an application is deployed at a company with no Oracle support stuff maintenance is easier this way.


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...

wiert 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.


Millan said...

Great Nice topic. its very interesting about Delphi. thanks for sharing .

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