Calling an Oracle stored procedure using NHibernate

1. The stored procedure must have an out sys_refcursor parameter as the first argument.
2. Tested with NHibernate 2.1.2 and Oracle 11g.
3. Needs Oracle.DataAccess (not System.Data.OracleClient). Tested with Oracle.DataAccess 2.111.6.0 AMD64.

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>

NHibernate mapping:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="SampleProject" namespace="SampleProject">
  <sql-query name="GetEmployeesByDepartmentId">
    <return class="Employee" />
    { call GetEmployeesByDepartmentId(:p_departmentid) }
  </sql-query>
</hibernate-mapping>

C# code:

var employees = nhibernateSession
    .GetNamedQuery("GetEmployeesByDepartmentId")
    .SetParameter("p_departmentid", departmentId)
    .List<Employee>();

To execute a stored procedure that has no out parameters:

nhibernateSession
    .CreateSQLQuery("call DoSomething(:p_parameter1)")
    .SetParameter("p_parameter1", parameter1)
    .ExecuteUpdate();

17 January 2012 | .NET, C# | Comments