NHibernate get data from Stored Procedure through ref_cursor

I'm also having trouble to get data from Stored Procedure through ref_cursor . Maybe you could advise how did you resolve that issue -

My error : "ORA-06553: PLS-306: wrong number or types of arguments in call to 'GET_SAMPLE_LIST'"} I suppose The types of arguments in call is the problem

Here is my code :

HBM

<?xml version="1.0" encoding="utf-8" ?>
<!-- Generated by MoreGen 28-Apr-2008 11:27:28 -->
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Infra"
               namespace="Infra.Entities">

<class name="Sample" table="MY_PACK.Get_Sample_List" lazy="true">
<id name="Id" column="ID">
  <generator class="native" />
</id>
<property name="Makat8" column="makat8" />
<property name="SerialNumber" column="serial_number" />

<loader query-ref="MY_PACK.Get_Sample_List"/>
</class>
<sql-query name="MY_PACK.Get_Sample_List" callable="true" >
<query-param name="p_crs" type="OracleDbType.RefCursor" />
<query-param name="p_1" type="int" />
<query-param name="p_2" type="string" />
<query-par开发者_开发百科am name="p_3" type="int" />
<query-param name="p_4" type="date" />
<query-param name="p_5" type="date" />
<return alias="MY_PACK.Get_Sample_List" class="Sample">
  <return-property name="Makat8" column="makat8" />
  <return-property name="SerialNumber" column="serial_number" />

</return>
call MY_PACK.Get_Sample_List  (:p_crs, :p_1 , :p_2 , :p_3 , :p_4 , :p_5)


</sql-query>
</hibernate-mapping>

The SAmple entity :

public class Sample
{
    public virtual int Id { get; set; }
    public virtual int Makat8 { get; set; }
    public virtual int SerialNumber { get; set; }
}

Session configuration - it's Fluent but still the same :

       m_Factory  = Fluently.Configure()  //cfgRules
              .Database(OracleClientConfiguration.Oracle10.Dialect("NHibernate.Dialect.Oracle10gDialect")
           .ConnectionString(x => x.FromConnectionStringWithKey("MyDBCONNSTRING"))
             .Driver<NHibernate.Driver.OracleDataClientDriver>().ShowSql())
             .ExposeConfiguration(c => c.Properties.Add("hbm2ddl.keywords", "none"))
            .Mappings(m =>
           {
               m.FluentMappings.AddFromAssemblyOf<MyEntityMapping>();
               m.HbmMappings.AddFromAssemblyOf<MyHBMMapping>();
               m.MergeMappings();
           })
           .BuildConfiguration()
           .BuildSessionFactory();

There's the test :

using (ISession Session = NHibernateSessionProvider.GetSession("MyDBCONNSTRING"))
        {
            using (NHibernate.ITransaction Tran =    Session.BeginTransaction(System.Data.IsolationLevel.Serializable))
            {
                IQuery query = (IQuery)Session.GetNamedQuery("MY_PACK.Get_Sample_List").SetResultTransformer(Transformers.AliasToBean(typeof(Sample))); ;



                query.SetParameter("p_crs", OracleDbType.RefCursor);
                query.SetParameter("p_1", 1);
                query.SetParameter("p_2", 12345678);
                query.SetParameter("p_3", 1);
                query.SetDateTime("p_4", null);
                query.SetDateTime("p_5", null);




                IList result = query.List();

            }
        }

Any help will be appreceated!

It's been a while since I've used NHibernate with Oracle, but I seem to remember something about the fact that it didn't work unless the name of the ref_cursor stored procedure parameter was a specific value (try to google along those lines), and was the first parameter of the proc (as yours is).

REF CURSOR must be the first parameter in your procedure (pCursor OUT ReferenceCursor):

Package Body:

PROCEDURE usp_GetDual
    (
          pCursor OUT ReferenceCursor,
          a IN CHAR,
          b IN CHAR
    )

  IS

    err_code NUMBER;
    err_msg VARCHAR2(200);

  BEGIN

  OPEN pCursor FOR
    SELECT * FROM dual;

   EXCEPTION
    WHEN OTHERS THEN 
        err_code := SQLCODE;
        err_msg := substr(SQLERRM, 1, 200);

END usp_GetDual;

and your mapping XML:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly">
    <sql-query name="GetDaul">
        { call MYPACKAGE.usp_GetDual ( :a, :b ) }
    </sql-query>
</hibernate-mapping>

Now you can simply query using this:

var value = Session.GetNamedQuery("GetDaul")
    .SetParameter<string>("a", "AAA")
    .SetParameter<string>("b", "BBB")
    .UniqueResult();

You can find a better explanation here.

This article might help you to understand things.