NHibernate connect to Oracle from .NET without a client installation

22 August 2013 markporter

Recently (for reasons that seem to ridiculous to document) I needed to see if I could use the 11.2 version of the Oracle client from an NHibernate application without impacting an installation of 11.1 already on the machine. After a little bit of investigation (and honestly some trial and error) discovered that the Oracle client can delivered as a packaged part of a .NET application. In my case this allowed the client to use a later version than installed on the server, but also allows the use of the Oracle client without having any client installed.

Firstly you need to deliver the necessary binaries to the bin directory, so from the Oracle downloadsget the right version of the xcopy install binary for your platform. In this case I was using the 32 bit version ODAC112030Xcopy_32bit. The binaries required are:

oci.dll from instantclient_11_2
Oracle.DataAccess.dll from odp.net4\odp.net\bin\4
oraociei11.dll from instantclient_11_2
OraOps11w.dll from odp.net4\bin

Without the standard TNSNAMES lookup process there are several alternative approaches to connection configuration:
 

Easy connect

The easy connect option allows you to supply enough to connect to the instance; this is limited to server, port and instance not allowing some of the extended features supported by local (TNS) naming. 

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.connection_string">Data Source=oracle-server:1521/orcl;Persist Security Info=True;User ID=SCOTT;Password=TIGER</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
  </session-factory>
</hibernate-configuration>

Embed the TNS entry in the connection string

You can embed the connection details from you TNSNAMES.ORA directly in the connection string. All you need to do is put the TNS entry directly in data source on a single line. 

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.connection_string">Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME = ORCL)));Persist Security Info=True;User ID=SCOTT;Password=TIGER</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
  </session-factory>
</hibernate-configuration>

This lets you use any additional TNS stuff (like load balancing), but is obviously pretty unwieldy from a deployment and maintenance perspective!

Local TNSNAME.ORA

You can actually deploy a TNSNAMES.ORA file into the bin directory and have connection details picked up from here. So the configuration will just use a data source with a TNS name value (in this case test).

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="connection.connection_string">Data Source=test;Persist Security Info=True;User ID=SCOTT;Password=TIGER</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>
  </session-factory>
</hibernate-configuration>

which refers to a value in TNSNAMES.ORA file delivered to the bin directory containing:

TEST =
 (DESCRIPTION = 
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-server)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORCL)
 )
)
Comments are closed