Calling an Oracle function using NHibernate

1. Tested with NHibernate 2.1.2 and Oracle 11g.
2. Needs Oracle.DataAccess (not System.Data.OracleClient). Tested with Oracle.DataAccess 2.111.6.0 AMD64.
3. If your Oracle function returns a scalar value of datatype number, the .NET object will be a Decimal.

NHibernate config:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.driver_class">
      NHibernate.Driver.OracleDataClientDriver
    </property>
  </session-factory>
</hibernate-configuration>

C# code:

var result = nhibernateSession
    .CreateSQLQuery("select GetSomeValue(:p_parameter1) from dual")
    .SetParameter("p_parameter1", parameter1)
    .UniqueResult();

// GetSomeValue returns a scalar of datatype number; result will be object of type Decimal

var someValue = Convert.ToInt32(result);

17 January 2012 | Uncategorized | Comments