CODEWITHSUNDEEP
oracle-databasenhibernatestored-procedures
publicgk
publicgk

Reputation: 3191

Oracle Stored Procedure with out parameter using Nhibernate

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.

Upvotes: 3

Views: 4301

Answers (5)

jlb83
jlb83

Reputation: 2198

Whilst looking into getting out parameters from NHibernate I found various links suggesting that to get a stored proc out parameter value (rather than using a refcursor) you need to construct the command in code rather than using the XML mappings file.

But I found difficulty finding a complete working example online. It's not a beautiful solution, but I post in case it helps someone.

  1. Example Oracle stored proc:

    create or replace PACKAGE BODY MY_PACKAGE AS

  PROCEDURE add_one
  (
    p_out out number,
    p_in in number
  ) AS
  BEGIN
    select p_in + 1 into p_out from dual;
  END add_one;

END MY_PACKAGE;

  2. Example C# code, where sessionFactory is an NHibernate ISessionFactory:

    using (var session = sessionFactory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var command = session.Connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "MY_PACKAGE.add_one";

    var parmOut = command.CreateParameter();
    parmOut.ParameterName = "p_out";
    parmOut.DbType = DbType.Decimal;
    parmOut.Direction = ParameterDirection.Output;
    command.Parameters.Add(parmOut);

    var parmIn = command.CreateParameter();
    parmIn.ParameterName = "p_in";
    parmIn.DbType = DbType.Decimal;
    parmIn.Direction = ParameterDirection.Input;
    parmIn.Value = 67;
    command.Parameters.Add(parmIn);

    transaction.Enlist(command);
    command.ExecuteNonQuery();

    int sixtyEight = (int) (decimal) parmOut.Value;
}

Upvotes: 1

Syd
Syd

Reputation: 1546

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!

Upvotes: 0

Blake Blackwell
Blake Blackwell

Reputation: 7795

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.

Upvotes: 1

Douglas Aguiar
Douglas Aguiar

Reputation: 84

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.

Upvotes: 2

Marius Burz
Marius Burz

Reputation: 4655

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.

Upvotes: 0

Related Questions