P3.NET

Using the Oracle Managed Driver

If you use .NET to talk to an Oracle database then you most likely are using ODP.NET. This is a .NET package provided by Oracle for communicating with their database. But using it has been historically difficult because of the dependencies. My company uses Oracle in a couple of applications and therefore uses ODP.NET but recently, as we are moving to .NET Core, we revisited this code and have been able to successfully upgrade to the newer ODP.NET Managed Driver.

Oracle Drivers

There are several different Oracle drivers available. The first driver is part of the .NET framework itself. It can be found under System.Data.OracleClient. This driver has been obsolete for years. There is no reason to use it anymore. It is curious that Microsoft has not yet removed it from the framework.

The next two drivers are from Oracle – ODP.NET Unmanaged Driver and ODP.NET Managed Driver. Purposefully, both drivers share the same APIs just in different namespaces. The unmanaged driver resides in Oracle.DataAccess.OracleClient while the managed driver lives in Oracle.ManagedDataAccess.OracleClient. You could technically use both versions in the same application but there would be no reason to do so. In both cases the drivers are a drop in replacement for the existing .NET Framework versions.

ODP.NET Unmanaged Driver

The unmanaged driver can be installed through the standard Oracle client installer. The unmanaged driver is not available from NuGet. There are some unofficial copies floating around and some companies, like mine, created a package for their internal NuGet repository. The reason that there is no NuGet package is because the unmanaged driver requires native components.

The unmanaged driver is just interop calls to the native components. Because of this the Oracle native client has to be installed. Furthermore since the driver is interoping to native code you have to target the platform appropriate for the client that is installed. While Oracle does support installing both x86 and x64 clients it is a little tricky in my experience. If you try to mix platforms then the driver will not work. Since the driver is platform-aware any application that tries to use it must also be platform aware. This can cause issues for some applications.

Another issue with the unamanged driver is configuration. Because it is interoping to native code you have to set up the native connections (e.g. tnsnames.ora). Anybody who has worked with Oracle is aware of how this works. For .NET applications we are used to putting connection strings into configuration files and being done. That doesn’t work with the unmanaged driver. The connection string you use is a pointer to the .ora file.

A final issue with the driver was fixed in newer versions (v11+). The original driver did not rely on the standard ADO.NET data types like DbConnection, DbCommand and DbParameter. If this didn’t matter to you then you would never notice. However if your code was written to be ADO.NET-aware then the driver would not work. In the case of my Kraken library I have a wrapper around ADO.NET databases. I had to create wrappers around the unmanaged driver types to make them appear as ADO.NET types. Here’s an example of the OracleConnection type that I used.

internal sealed class OracleDbConnection : DbConnection
{
   public OracleDbConnection ( string connectionString ) { _connection = new OracleConnection(connectionString); }
   public OracleDbConnection ( OracleConnection connection ) { _connection = connection ?? throw new ArgumentNullException("connection"); }

   public OracleConnection InnerConnection => _connection;

   protected override void Dispose ( bool disposing )
   {
      base.Dispose(disposing);

      if (disposing)
         _connection.Dispose();
   }

   protected override DbTransaction BeginDbTransaction ( IsolationLevel isolationLevel )
         => new OracleDbTransaction(m_connection.BeginTransaction(isolationLevel));
   ...
}

In the newer versions of the client the Oracle types do derive from ADO.NET types so the above wrapper types simply go away.

The native client in combination with the external configuration makes using this driver painful. The platform-aware code is inconvenient. The only real advantage this driver has is that it works with older versions of Oracle.

ODP.NET Managed Driver

The managed driver is a 100% managed code implementation for Oracle. It has no external dependencies and can be installed via NuGet. Because it is managed code it can be run against x86 or x64. Since there is no native components, external configuration is not necessary either. It can all be done through the configuration file.

The managed and unmanaged drivers share mostly the same API except in different namespaces. There are some missing members as documented here but I suspect the vast majority of applications won’t notice. In theory any application that is currently using the unamanged driver should be able to switch to the managed driver with little change other than configuration. One exception is with XML. The managed driver seems to have limited XML support.

The managed driver appears to work with most versions of Oracle 10.2 and higher. You should do testing to ensure it works with your version however.

Migrating to the Managed Driver

Since the managed driver is more flexible it makes sense to switch. The migration process is straightforward. Here’s some sample code using the unmanaged driver.

using Oracle.DataAccess.OracleClient;

using (var conn = new OracleConnection("DataSource=MyOracle;User Id=user;Password=password"))
{
   var cmd = conn.CreateCommand();
   cmd.CommandText = "SELECT first_name, last_name FROM employees WHERE payRate >= :payRate";
   cmd.Parameters.Add(":payRate", OracleDbType.Double, 12);

   conn.Open();
   using (var reader = cmd.ExecuteReader())
   { };
};

To get this code to work the Oracle client would need to be installed, a .ora file needs to be created containing MyOracle, the application needs to share the platform bitness of the client and a reference to the Oracle.DataAccess.OracleClient assembly is needed.

The nice thing about the managed driver is that your code remains unchanged, generally. To use the managed driver.

  1. Remove any references to the Oracle.DataAcces.OracleClient assembly.
  2. Using NuGet add a dependency on Oracle.ManagedDataAccess.
  3. Replace all using statements of Oracle.DataAccess.OracleClient to Oracle.ManagedDataAccess.OracleClient.
  4. Recompile

The bigger changes are going to be around the configuration. Since there is no native components all the configurations are done in the config file. Depending upon how much custom configuration you already have determines what you need to change. The official documentation is here. For most applications the following changes are sufficient.

  1. Add a section handler for oracle.manageddataaccess.client.
  2. Replace the existing Oracle DbProvider with the new managed version.
  3. Configure the client.
  4. Optionally configure the application to no longer be platform-aware.

This is an example configuration.

<configuration>   
   <configSections>
      <!-- Add section handler >
      <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
   </configSections>

   <system.data>
      <!-- Replace existing provider -->
      <DbProviderFactories>
         <remove invariant="Oracle.ManagedDataAccess.Client"/>
         <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
      </DbProviderFactories>
   </system.data>

   <!-- Configure -- >
   <oracle.manageddataaccess.client>
      <version number="*">
         <settings>
            <!-- <setting name="TNS_ADMIN" value="C:\Oracle\Products\version\network\admin\tnsnames.ora" /> -->
         </settings>
         <dataSources>
            <dataSource alias="MyOracle" descriptor="(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1234)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MyOracle) ) )"/>          
         </dataSources>
      </version>
   </oracle.manageddataaccess.client>
</configuration>

The configuration is the interesting part. The managed driver can still use the unmanaged configuration in the .ora file if you have one. It uses an algorithm to try to find it (see the documentation). It starts in the application’s directory and moves to the ORACLEHOME path. So if you have a mixed environment you can stick with .ora files. But if you don’t need the native client you can remove it and the .ora file. Instead the data source information in the .ora file can be placed in the configuration section for the driver. If you want to use an external .ora file that isn’t found in the search path then configure the TNS_ADMIN setting instead.

With the above changes made you can rerun your application and it should now be using the managed driver. In my experience the managed driver is just as fast as the unmanaged driver and I saw no issues.

There is only one thing to be aware of with the managed driver and it has to do with parameter binding. I don’t remember having to do anything special with the unmanaged driver but the managed driver binds by position by default. What this means is that the name you use for a parameter is complete irrelevant. For example this code works.

var cmd = new OracleCommand();
cmd.CommandText = "SELECT * FROM employees WHERE id = :does_not_matter";
cmd.Parameters.Add(":some_name", OracleDbType.Int32, 123);

But this also means that the parameters must be added to the command in the exact order they appear in the query. This is not how other providers work so you’ll want to bind by name instead. To do that you need to set the BindByName property to true on the command.

var cmd = new OracleCommand();
cmd.CommandText = "SELECT * FROM employees WHERE id = :does_not_matter";

//This won't work anymore
cmd.Parameters.Add(":some_name", OracleDbType.Int32, 123).BindByName = true;

Oracle Driver Future

Oracle is working on a .NET Core implementation of ODP.NET. It is still in beta but will be managed only. Since .NET Core is the future applications should go ahead and migrate to the managed driver now and then plan to migrate to the newer .NET Core driver later.

Oracle is also working on an Entity Framework-compatible version if you are using EF in your code.