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

One Response to “Calling an Oracle stored procedure using NHibernate”

  1. 1 Sigmund 25 July 2013 @ 3:53 am

    With :

    NHibernate 3.3.3
    Oracle 10g
    Oracle.DataAccess 2.102.2.20

    it doesn’t work.

    I always get the error message :
    “Unable to find specified column in column set (ResultSet).”

    It was working perfectly with NHibernate 2.1.2, but now that I’ve upgraded to 3.3.3 it doesn’t work anymore.

    Dammit !

Comments:

  1.  
  2.  
  3.