NHibernate to access Oracle stored procedure REFCURSOR and output parameter

Question

Does the current version of NHibernate (v2.1.2) support access Oracle stored procedure output REFCURSOR in addition to an output parameter?

I can access the output refcursor fine with my code. However i'm not sure i can access the additional output param in the same stored procedure.

Some sample of calling syntax would be greatly appreciated. thanks.

score 1 · Accepted Answer · answered Jul 22 '10 at 20:04

Nope it does not. Only one refcursor is supported and it has to be the first parameter in the sproc.

You can always get the IDbConnection from the session and then use plain ODP.Net for such scenarios (you lose nh functionality) or rather change the stored procedure.

score 0 · Answer 2 · answered Dec 20 '10 at 17:57

I found a solution to call old stored procedures with NHibernate.

I don't think that is the better way, but we normally don't have time to refactor everything, so:

using (ITransaction transaction = _session.BeginTransaction()) {
    IDbCommand command = new OracleCommand();
    command.Connection = _session.Connection;

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "pk_package.pr_procedure";

    // Set input parameters
    var param1 = new OracleParameter("@param1", OracleDbType.Decimal) {Value = someField};
    var param2 = new OracleParameter("@param2", OracleDbType.Decimal) {Value = 1};

    command.Parameters.Add(param1);
    command.Parameters.Add(param2);

    // Execute the stored procedure
    command.ExecuteNonQuery();
    transaction.Commit();
}

NHibernate to access Oracle stored procedure REFCURSOR and output parameter

2 Answers2