title: How do I use Oracle from .NET?
body: <p>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.</p>
  
  <p>So here's how far I've got.</p>
  
  <p>I filled out a huge form about my life at Oracle.com and downloaded the </p>
  
  <p><strong>ODBC Windows 64-bit Instance Client, 11.2.0.3.0</strong> and unzipped it into a folder.
  <strong>Basic Windows 64-bit Instant Client, 11.2.0.3.0</strong> and unzippped it into the same folder as above.</p>
  
  <p>I ran the install .exe (no MSI, remember this is 1997).</p>
  
  <p>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!</p>
  
  <p>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.</p>
  
  <p>I added a reference from my project to <strong>Dependencies\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll</strong></p>
  
  <p>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 <strong>Dependencies\odp.net4\bin\OraOps11w.dll</strong> but it could be some other Oracle binaries somewhere?</p>
  
  <p>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.</p>
  
  <p>Here is the config file section.</p>
  
  <pre><code>&lt;oracle.dataaccess.client&gt;
    &lt;add key="DllPath" value="D:\Trunk\Dependencies\odp.net4\bin" /&gt;
  &lt;/oracle.dataaccess.client&gt;
  </code></pre>
  
  <p>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!</p>
  
  <p>Can anyone advise me? Does anyone know a link to a Dummies Guide to Querying an Oracle View from .NET?</p>
  
  <p>Is there something I can do in 1997 to stop myself having to use Oracle in 2012?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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.</p>
  
  <p>I now get the following error, which I'm researching.</p>
  
  <blockquote>
    <p>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.</p>
  </blockquote>
  
  <p>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.</p>
  
  <p><strong>UPDATE 2</strong></p>
  
  <p>Registering the Oracle.DataAccess.dll library in the GAC didn't make any difference and I've unregistered it.</p>
  
  <p>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).</p>
  
  <p><strong>UPDATE 3</strong></p>
  
  <p>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.</p>
  
  <p>Unfortunately I get this error when browsing to my site hosted by the 64-bit IIS Express.</p>
  
  <blockquote>
    <p>Handler "ExtensionlessUrl-Integrated-4.0" has a bad module
    "ManagedPipelineHandler" in its module list</p>
  </blockquote>
  
  <p>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.</p>
  
  <p><strong>UPDATE 4</strong></p>
  
  <p>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.</p>
  
  <p>Now I am looking into this error when instantiating a new OracleConnection.</p>
  
  <blockquote>
    <blockquote>
      <p>The provider is not compatible with the version of Oracle client</p>
    </blockquote>
  </blockquote>
  
  <p><strong>UPDATE 5</strong> I think I've done it.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>So, to recap, for IIS Express 7.5 (which is a 32-bit process) on Windows 7, 64:</p>
  
  <ul>
  <li><p>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.</p></li>
  <li><p>Download and extract the 32-bit ODP.NET to some folder.</p></li>
  <li><p>Copy the OraOps11w.dll to the main folder in which the Instant Client resides (above).</p></li>
  <li><p>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.</p></li>
  <li><p>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.</p></li>
  </ul>
  
  <p>That should be it. Sounds fairly simple when you know how.</p>
  
  <p><strong>UPDATE 5.1</strong></p>
  
  <p>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.</p>
  
  <blockquote>
    <blockquote>
      <p>ORA-12541: TNS:no listener</p>
    </blockquote>
  </blockquote>