Oracle Stored Procedure with out parameter using Nhibernate

Question

How can I access the value of an out parameter of an oracle stored procedure in the .net code - Oracle stored procedure being called via Nhibernate?

Sample working code would help.

Answer 1

See this comment by Richard Brown. Some sample code can be found here.
Unfortunately I can't test it so I don't know whether it works or not.

Answer 2

You have to use the latest version of NHibernate (2.1.2).

<sql-query name="ReturnSomethig" callable="true">
   <return class="Somethig" />
   { call ReturnSomethig(:someParameter) }
</sql-query>

The Oracle Stored Procedure need to has the first parameter as a out sys_refcursor parameter.

And you can call the named query like that:

IQuery query = currentSession.GetNamedQuery("ReturnSomethig");
query.SetInt64("someParameter", someParameter);
var somethig = query.List<Somethig>();

And it will work.

Answer 3

I tried the 2.1.2 libraries without much luck. I had to actually make some modifications to the library based on this article. If you go this route, you'll want to make sure you are using the Oracle.DataAccess dll since it won't work with System.DataAccess.OracleClient dll.

Answer 4

A belated reply.

For oracle, out parameter is not supported by nHibernate unless it is a cursor. If you just want a scalar value, a work around is to wrap your stored procedure with an oracle function.

Then you can do this

  <sql-query name="TestOracleFunction" callable="true">
    <return-scalar column="MyOutputValue" type="String" />
    <![CDATA[
    select MyOracleFunction as MyOutputValue from dual
  ]]>
  </sql-query>

This works!