.Net and Oracle 10. Welcome to DLL Hell

Until Oracle 8 or 9 you installed the client, chose whether you want to use Oracle Driver (Oracle.DataAccess.dll) or the Microsoft Driver (System.Data.OracleClient) and you had a happy connection to your database.

Installing a OracleClient and connecting to a Oracle Database with was until a day in August 2008 a Non-Event. 15 minutes and off you go.

Now we upgraded our database backend to Oracle 10 as anything else is running out of maintenance.

"Ok", I thought. "Business as usual". "Install the latest client, turn on, works".

What I didn't know was that I was standing on the doorstep to Oracle DLL Hell.

First everything looked just smooth. After upgrading the client from Oracle 8 to 10, the connection with System.Data.OracleClient to our Production environment on Oracle 9 was just fine. No problems here.

The problems started when I first tried to connect with an Oracle 10 the same database only on Oracle 10.

4 out of five queries worked fine but then

"ORA-01405: fetched column value is NULL" System.Data.OracleClient

The error message means basically that I tried to fetch a null value into a PL-SQL variable.

Only Problem here. I use a common SQL here. So no fetch anywhere.

We now invested much much time in solving this problem, upgraded the server to latest patchlevel and lots of different things. Nothing helped. So the only way to solve this problem was to switch to the native Oracle .NET driver.

After switching to the native Oracle Driver (Oracle.DataAccess.dll) the problem was solved. Only problem that remained now, was that my version of the client 9 was not installed on the server and hence I would not be able to install the software on the server with that dll.

Ok, I thought. Lets get rid of my Oracle 9 and install a Oracle 10.2.0.3 client. The same client as installed on the server.

Sooner said than done. I chose "Application Developer" option and off I went.

But when I tried to add the reference to the Oracle .Net driver (Oracle.DataAccess.dll) using the "Add reference" dialog in Visual Studio .Net 2008 I found.... nothing. Obviously .Net Application Development is no longer in focus when installing the Oracle Client, but I knew that Oracle has special packages for .Net.

Installing Oracle Data Provider for .Net (10g).

Why the fuck the installer recommends to create a new Oracle Home instead of installing it where it belongs (the exsting Oracle Home of my 10.2.0.3 client directory)?

Very important: At the point where you can choose the target directory, make sure you select the existing client directory, by selecting the proper Oracle Home Name.

I cost me 1.5 days to find out, that by default, the installer creates a new Home Directory, where it simply doesn't do anything but to throw exceptions.

After installing the ODAC correctly you find it in your "Add reference" Dialog unter .NET components under "Oracle.DataAccess".

5 Cents on .NET and Oracle 10

The story is longer but here are my 5 cents:

• Make sure the Oracle client version on your development and production machine are the same
• To get a working .NET driver, you need to additionally install the Oracle Data Access Components for .NET which can be found here .
• Make sure, you select the proper version of the ODAC for your client.
  
• When the bloody Oracle installer asks you for the directory make sure you select the existing directory of your client.

Dear guys at Oracle. In the last 2 days I really learned to hate your "Oracle Universal Installer". The setups are full of flaws, misleading informations, bugs, crc errors, bad recommendations or unnecessary components.
And why the hell, the Developer Oracle Client Package does not include a .Net Driver?
By today I can call myself Oracle Client Setup Expert (OCSE), for a thing that should be what it is: a non-event