How do I use Oracle from .NET?

Question

Unfortunately, I'm trying to use Oracle from .NET and it's like going to back to 1997. Explaining things properly is a sign of weakness and the registry and environment variables seem to feature.

So here's how far I've got.

I filled out a huge form about my life at Oracle.com and downloaded the

ODBC Windows 64-bit Instance Client, 11.2.0.3.0 and unzipped it into a folder. Basic Windows 64-bit Instant Client, 11.2.0.3.0 and unzippped it into the same folder as above.

I ran the install .exe (no MSI, remember this is 1997).

I added a TNS_NAMES environment variable pointing at the corporate TNSNames.ora file on a network share and setup a quick DSN in Control Panel and ran the test - it worked!

I then downloaded the XCOPY version of the latest ODP.NET for .NET 4.0 and copied it into my Dependencies folder under my source control workspace.

I added a reference from my project to Dependencies\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll

I've read that I need to let the managed ODP.NET stuff know where it can find its unmanaged Oracle libraries, which I'm guessing is Dependencies\odp.net4\bin\OraOps11w.dll but it could be some other Oracle binaries somewhere?

Now I'm setting a config file parameter called DllPath. Sadly, the Oracle docs seem not to realise that .NET configuration files need their sections hooking up in the configSections section.

Here is the config file section.

<oracle.dataaccess.client>
  <add key="DllPath" value="D:\Trunk\Dependencies\odp.net4\bin" />
</oracle.dataaccess.client>

What goes in the configSection? The few threads I've found on this give an example and then say "I don't know what type= should be", which is the main bit!

Can anyone advise me? Does anyone know a link to a Dummies Guide to Querying an Oracle View from .NET?

Is there something I can do in 1997 to stop myself having to use Oracle in 2012?

UPDATE

Just a quick update. I've copied OraOps11w.dll into my output bin folder and removed any web.config file changes I'd previously added.

I now get the following error, which I'm researching.

Could not load file or assembly 'Oracle.DataAccess' or one of its dependencies. An attempt was made to load a program with an incorrect format.

Some other forums talk about registering Oracle.DataAccess.dll in the GAC, which I'm keen to avoid - and I can't see what the difference would be anyway, but I'll give it a go.

UPDATE 2

Registering the Oracle.DataAccess.dll library in the GAC didn't make any difference and I've unregistered it.

The end of the working week is upon me and I have to catch a train, but I've emailed another dev team here and hope they've fought this battle before (and not used nightly extracts like everyone else).

UPDATE 3

This morning I downloaded and setup IIS Express 8.0 RC which has a 64-bit version. The 64-bit version is not supported from VS2010 so it needs to be run from the command line. This was fairly simple, by looking in Task Manager, I was able to see what command line VS uses for iisexpress.exe *32.

Unfortunately I get this error when browsing to my site hosted by the 64-bit IIS Express.

Handler "ExtensionlessUrl-Integrated-4.0" has a bad module "ManagedPipelineHandler" in its module list

This is a problem too far down this line of enquiry so I'm going to try using the 32-bit version of ODP.NET.

UPDATE 4

I downloaded the full install of the 32-bit version but I got scared when I saw the Oracle Universal Installer. It looks too 90s to trust with my precious dev box. So I'm using the 32-bit 'XCopy' client, this lets me to bring up my site's homepage, so references are working. Thanks for those that suggested an architecture mismatch 32/64 problem.

Now I am looking into this error when instantiating a new OracleConnection.

The provider is not compatible with the version of Oracle client

UPDATE 5 I think I've done it.

So I downloaded the 32-bit latest 11g "Instant Client" and just put it in my \Dependencies folder for the solution trunk codebase. I added this folder to the %PATH% system environment variable and also chucked the OraOps11w.dll file in there.

Finally, _oracleConnection = new OracleConnection(connectionString) doesn't throw! Now I need to make sure I've a bottle of Laphroaig on the desk when I come to deploy to prod.

So, to recap, for IIS Express 7.5 (which is a 32-bit process) on Windows 7, 64:

• Download and extract the 32-bit 11g "Instant Client" to some folder and add this location to your PATH. Oci.dll is the main Oracle client-side unmanaged library.

• Download and extract the 32-bit ODP.NET to some folder.

• Copy the OraOps11w.dll to the main folder in which the Instant Client resides (above).

• Add a TNS_ADMIN environment variable pointing to a folder in which TNSNames.ora text file resides. This is like a hosts file and maps Oracle service names to server host names.

• Close and re-open Visual Studio to ensure you pick-up the new environment variables, and add a reference to the Oracle.DataAccess.dll managed assembly in your ODP.NET folder.

That should be it. Sounds fairly simple when you know how.

UPDATE 5.1

Investigating the error below, which I originally didn't report since I was sure it was a firewall issue. The company I'm at has two on each workstation, but I just opened a raw socket to the Oracle server so it can't be.

ORA-12541: TNS:no listener

Answer 1

I have had the same problem and found that running the unconfigure and configure bat files in the copy version of the Oracle.DataAccess.dll 11.x software install may solve or help solve the problem.

E.G. configure.bat xxx.netx xxx\product\11.2.0\client_1

Answer 2

The simplest way to explain the issue is to point out the difference between the Oracle Client and the ODAC client libraries.

On a 64-bit machine (Windows 7+), you need to have installed the 64-bit Oracle Client. This is the client your machine will use to connect to Oracle databases that are deployed locally on your machine. For apps hosted on a web server or other terminal server, the rule is the same.

The trick is this... as a developer, our machines must also have the .Net IDE tooling. Oracle has a crappy naming convention but essentially there are 2 pieces: ODT (tooling) and ODAC (data access). The ODP.Net data provider is a part of the ODAC libraries.

So... back to the IDE... Visual Studio is 32-bit and therefore we must install the tooling above in 32-bit.

While developing, debugging, etc. VS.Net is using the 32-bit client libraries and data access libraries to work with Oracle.

As soon as you deploy this application to a machine, it uses whatever Client is loaded on the machine unless a specific platform is targeted.

This means if you target 32 and deploy to 64, it will break... and vice versa. The best thing to do is leave it on the any platform section, and simply remember what the hell you are doing :)

The other thing to be careful with is to ensure both your Client and ODAC packages are of the same version... you do not want to have 11g R2 client, and 11g R5 ODAC, because as soon as you deploy, crap breaks again.

---

The caveat here is if you wish to "embed" the oracle client inside your application, in which case OraOps alongside several other libraries are deployed with the application - this is called Oracle's Instant Client, and is also a part of the ODAC package and included in their full blown Client package as well.

---

The good news...

Oracle is soon (2013 Q1) to release their next ODP.Net package... which will be a fully managed-code library... meaning no more separate client or ODAC package to match up, and the platform will be ignorant of 32 and 64 bit distinctions... it will function much like the old microsoft library will, only it will be built and maintained by oracle with a more robust feature set. I only wish it would arrive sooner.

Answer 3

I've spent days in the past trying to get Oracle connected in Windows 7.

The problem I encountered was that I had a 32-bit ASP.NET application with the 32-bit Oracle ODP.NET drivers installed. I wasn't aware that, even though the ASP.NET application runs as a 32-bit process and requires the 32-bit ODP.NET, IIS is a 64-bit process and causes ODP.NET to access the 64-bit registry keys. You didn't mention whether you were trying to setup Oracle for ASP.NET, but I'll share my fix (although it is for the Oracle 10.2 install).

(I'm copying this verbatim from my blog post)

Note: this forces Oracle 11.2.0.1 to use 32-bit only in a 64-bit environment.

The first mistake I made was to install the Oracle 10g client on Windows 7. This WILL NOT work. If you install it, you will have to perform some cleanup afterward.

• Go to Oracle's site and download/install the 11g 32-bit and 64-bit clients.
• Go here and follow the "Dummy registry entry" steps.
• Open regedit and verify that HKLM\SOFTWARE\Wow6432Node\ORACLE\inst_loc points to the proper location (probably C:\oracle\Inventory)
• Point the registry entry at HKLM\SOFTWARE\Wow6432Node\ORACLE\VOBHOME2.0 to the 32-bit Oracle folder
• Create an ORACLE_HOME environment variable and point it to the 32-bit folder.

Now you should be all set after a restart.

If you tried installing the Oracle 10g client, you'll have to perform some cleanup. First, make sure Oracle 10g is completely uninstalled, dereferenced from your PATH variable, and the directory is removed. Then, find the assemblies referenced in the GAC:

gacutil /l | find /i "Oracle" > c:\[some directory]\oracle.txt

Find the lines referring to Oracle.DataAccess version 10.2 and remove them from the GAC. As an example (this won't work unless you have the same version installed):

gacutil /u "Oracle.DataAccess, Version=10.2.0.100, Culture=neutral, PublicKeyToken=89b483f429c47342"

When I did this, I had three assemblies to remove:

1. Oracle.DataAccess
2. Policy.10.1.Oracle.DataAccess
3. Policy.9.2.Oracle.DataAccess

Again, you'll probably need to restart (hey, it's Windows).

---

If you need to perform any cleanup, I've also blogged about that

For reference
In case the link to the "Dummy registry entry" steps dies, here's the text from that post by Gadi:

"" Hi Ben,

At design time, when working with the SQL Server Business Intelligence Development Studio, the Oracle Connector is running in 32-bit mode and therefore it loads the 32-bit Oracle client.

Most likelly, you are getting the error because the connector is loading the wrong Oracle client (oci.dll), where the TNS service is not defined.

To solve the problem, you have two options:

1. Specify the Oracle connection string, instead of the TNS service name in the Oracle Connection Manager Editor, for example, in the following format: host:port/service_name

2. Define a dummy registry entry (Z_SSIS) as follows:

   • Open the regedit utility.
   • Locate the following Key: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE.
   • Right-Click on ORACLE node and click on New -> Key.
   • Call the new key Z_SSIS (to make sure it's the last entry).
   • Right-Click on the Z_SSIS node and click on New -> String.
   • Name the property ORACLE_HOME.
   • Double-Click on the ORACLE_HOME and set it to the location of the Oracle 32Bit installation home directory you whant to use.

Regards,

Gadi ""

Answer 4

I don't believe this is a path issue. "An attempt was made to load a program with an incorrect format." is an error that almost always means that you are mixing 32-bit and 64-bit assemblies and DLLs in .NET.

I noticed you installed the 64-bit Oracle client, I am assuming since the DSN creation worked that you are on a 64-bit version of the OS? Your problem could be from a number of related scenarios:

• Your copy of OraOps11w.dll could be a 32-bit assembly and you are using it in a 64-bit app
• Your copy of OraOps11w.dll could be a 64-bit assembly and you are trying to use it from a 32-bit app
• one of the unmanaged dependencies of the Oracle Data Provider for .NET is 32-bit and is failing to load into your 32-bit app (or vice versa)

I believe if your app targets x86 specifically, you must install the 32-bit Oracle client package regardless of the underlying OS platform (e.g. you still would need the 32-bit Oracle client for a 32-bit app even if you were using a 64-bit version of Windows)

a couple of things to check:

• Check your "Platform Target" setting in your assembly that calls the Oracle provider: x64 will always try to run as 64-bit, x86 will always try to run as 32-bit and "Any Platform" will JIT compile to the target OS architecture at runtime (32-bit on an x86 system and 64-bit on a x64 system)
• Run OraOps11w.dll through corflags.exe (see details at this SO post) to see if it is 32-bit, 64-bit or "Any CPU"

Lastly, have you tried to built-in .NET data provider for Oracle (System.Data.OracleClient namespace?)

That's the best I can do since I don't know more about your projects settings for your app.

Answer 5

First off, +1 for your unadulterated love of Oracle, and realizing that they are the best company with the best products! ;)

---

Yeah the download and install process is ridiculous. I usually can't even find the right client download on their site, and the Oracle Universal Product Installer thing is terrible.

---

Sometimes you will need a copy of OraOps11w.dll in your \bin directory. Sometimes not. I've never figured out when its needed and when it isn't, but I think that if your referenced Oracle.DataAccess.dll isn't exactly the same version as the installed Oracle client, then you need the OraOps11w.dll.

---

Can you explain what you are trying to do in your config file with DllPath? The Oracle install should have registered just about everything it needs in the machine.config for your .NET Runtime. It has been a while since I set up ODP.NET, but I don't remember there being much to put in the app.config, other than the connection string.