Calling an Oracle store procedure with nHibernate

Question

I've got a stored procedure in Oracle:

procedure Test(results OUT gencursor, id in number) is
  v_cursor gencursor;
begin
OPEN v_cursor FOR
          select id, name, age from tblcustomers s where s.id = id;
results:=v_cursor;
end Test;

Now, i'd like to execute this procedure using nHibernate ISession.CreateSQLQuery. All the examples I've seen until now use ISession.GetNamedQuery().

So, I'd want to do something like (ToDataTable is my own extension method on IQuery, I have more extension methods which I'd want to stay using in combination with stored procedures):

        var result = session
            .CreateSQLQuery("call MyPackage.Test(:id)")
            .SetDecimal("id", 33)
            .ToDataTable();

The code above throw the following exception:

"could not execute query [ call MyPackage.Test(?) ] Name:id - Value:33 [SQL: call MyPackage.Test(?)]"

I've also tried:

        var result = session
            .CreateSQLQuery("call MyPackage.Test(:result, :id)")
            .SetDecimal("id", 33)
            .ToDataTable();

That one throw the exception:

Not all named parameters have been set: [result] [call MyPackage.Test(:result, :id)]

Answer 1

I don't now if you can call sps from CreateSQLQuery, but I'll show you how we call Oracle sps from NHibernate in my current project:

var query =  _session.GetNamedQuery("MyPackageTestSp");
            query.SetParameter("id", 33);
            query.ExecuteUpdate();

In a mapping file the named query is defined like so:

<sql-query name="MyPackageTestSp">
    call call MyPackage.Test(:result, :id)
</sql-query>

Works like a charm :-)

Answer 2

I've found it easier to just use a view. In your mapping, just set mutable=false on your class so it can't be updated. The view should create the level of abstraction your are trying to achieve.