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();
     .ConnectionString(x => x.FromConnectionStringWithKey("default"))
    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
  insert into cpa.dev_int (i10, i11) values (a, b);

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;

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);

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

2 thoughts on “Oracle Stored Procedures with NHibernate

  1. Pingback: Generating a Stored Procedure Wrapper for NHibernate in PL/SQL « devioblog

  2. Pingback: NHibernate accessing the SP getting errors – NHibernate.Loader.Loader.DoList ?–no solution yet! anyone? « Kaidanov Tzvi Gregory Technical Issues & Solutions

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.