Oracle Stored Procedures with NHibernate

Setting up Oracle access in NHibernate (using Fluent NHibernate) was pretty straight-forward (with the exception of some nasty error messages). (See part 1, part 2)

The last missing item so far is executing Oracle stored procedures via NHibernate.

I found this blog most useful on mapping stored procedures to queries in NHibernate. The idea is to encapsulate the stored procedure call inside a named SQL query.

In Visual Studio, the .hbm.xml files need to have a Build Action of “Embedded Resource”, and the option “Copy To Output Directory” set to “Do Not Copy”. Thus they end up inside the compiled assembly and can be retrieved by the mapper.

Since my NHibernate setup is configured using Fluent, I needed to merge the Fluent configuration with the .hbm.xml files that the stored procedure declarations require. (As far as I know, Fluent does not support stored procedure declarations)

Thanks to this blog, it’s quite simple to merge various mapping mechanisms (in this case Fluent and Hbm), and the final code to create an NHibernate SessionFactory looks like this:

public class ConfigureDAL
{
  public static ISessionFactory Configure()
  {
    var c = Fluently.Configure();
    c.Database(OracleDataClientConfiguration.Oracle10
     .ConnectionString(x => x.FromConnectionStringWithKey("default"))
     .DefaultSchema("cpa"));
    c.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ConfigureDAL>());
    c.Mappings(m => m.HbmMappings.AddFromAssemblyOf<ConfigureDAL>());
    return cfg.BuildSessionFactory();
  }
}

Let’s take a simple stored procedure as example:

create or replace procedure cpa.test_store_int2(a number, b number) is
begin
  insert into cpa.dev_int (i10, i11) values (a, b);
end;

This procedure inserts a record of two integers into a table, and returns no values. This is the .hbm.xml mapping file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  assembly="myassembly" namespace="myassembly.mynamespace">
  <sql-query name="TestStoreInt2" callable="true">
  <query-param name="Value1" type="int" />
  <query-param name="Value2" type="int" />
begin cpa.test_store_int2(:Value1, :Value2); end;
  </sql-query>
</hibernate-mapping>

There are several things to note:

• The parameters must be listed in <query-param> nodes
• Nothing is returned, so we do not need a <return> declaration
• The stored procedure call must include the parameters using :name notation
• The stored procedure call must be wrapped inside a BEGIN END; block

If you omit the BEGIN/END, Oracle will raise the error ORA-00900 (invalid SQL statement).

The stored procedure is executed via the IQuery.List() method:

using (var session = sessionFactory.OpenSession())
{
  IQuery sp = session.GetNamedQuery("TestStoreInt2");
  sp.SetParameter<int>("Value1", 101);
  sp.SetParameter<int>("Value2", 202);
  sp.List();
}

This pattern calls for encapsulation into a (generated) method, of course. (to be continued)