nhibernate, call function in Oracle which returns sys refcursor

I am trying to call a function (oracle) using nhibernate that return ref cursor, but i am not successful with the hbm file, can any one please guide me with this.

If i make it like <return class ... I am getting configuration error.

I tried { 开发者_开发知识库? = call package.function(:a, :b, :c) as result from dual }, even this is also not working.

There are some limitations when calling ORACLE functions/procedures with nHibernate.
As stated in the reference documentation (17.2.2.1):

For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type, see Oracle literature.

I've tried to play a little bit with it as I am having the same problem.

Here is the PACKAGE-PROCEDURE:

HEAD:

create or replace
PACKAGE           "MYPACKAGE" AS

    TYPE ReferenceCursor IS REF CURSOR;

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

END MYPACKAGE;

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;

This my mapping file:

<?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>

and this is the code I've used to test it:

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

As you can see the REF CURSOR must be the first parameter in your procedure (pCursor OUT ReferenceCursor) and you do not need to reference it in your mapping or your call.

If you want to return entities, things get a little bit more complicated.

Your mapping file must specify the return type (class):

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly">
    <sql-query name="GetOrders">
         <return class="MyAssembly.Domain.MyOrder, MyAssembly" />
         { call MYPACKAGE.usp_GetOrders ( :pCompanyCode , :pOrderNumer ) }
    </sql-query>
</hibernate-mapping>

You have to define your entity:

public class MyOrder
{
    public virtual string Number { get; set; }
    public virtual int Ver { get; private set; }
    public virtual string Company { get; set; }
    public virtual string Customer { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;
        Order order = obj as Order;
        if (order == null)
            return false;
        if (this.Number.Trim() == order.Number.Trim() &&
            this.Ver == order.Ver &&
            this.Company.Trim() == order.Company.Trim()
            )
            return true;
        else
            return false;
    }

    public override int GetHashCode()
    {
        int hash = 0;
        hash = hash +
            (null == this.Number ? 0 : this.Number.GetHashCode())
            +
            (this.Ver.GetHashCode())
            +
            (null == this.Company ? 0 : this.Company.GetHashCode());

        return (hash);
    }
}

and this is the mapping file for your entity:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MyAssembly" namespace="MyAssembly.Domain">
  <class name="MyOrder" table="OCSAORH" mutable="false">
    <composite-id>
      <key-property name="Number" column="OCHORDN" type="String" length="10"></key-property>
      <key-property name="Ver" column="OCHAMND" type="Int32"></key-property>
      <key-property name="Company" column="OCHCOSC" type="String" length="5"></key-property>
    </composite-id>
    <property name="Customer" column="OCHCLII" type="String"></property>
  </class>
</hibernate-mapping>

This is my ORACLE package:

PROCEDURE usp_GetOrders 
          (
          pCursor OUT ReferenceCursor,
          pCompanyCode IN CHAR,
          pOrderNumer IN CHAR
      )

  IS

    err_code NUMBER;
    err_msg VARCHAR2(200);

  BEGIN

  OPEN pCursor FOR
       SELECT 
            OCSAORH.*
      FROM OCSAORH 
            WHERE OCSAORH.OCHAMND = 0
                AND OCSAORH.OCHCOSC = pCompanyCode
                AND OCSAORH.OCHORDN = pOrderNumer;              
    EXCEPTION
            WHEN OTHERS THEN 
          err_code := SQLCODE;
          err_msg := substr(SQLERRM, 1, 200);

END usp_GetOrders;

And now you can easily get your orders using parameters:

var listOfOrders = Session.GetNamedQuery("GetOrder")
    .SetParameter<string>("pCompanyCode", "ABC")
        .SetParameter<string>("pOrderNumer", "XYZ")
        .List<Domain.MyOrder>();

This article helped me to understand how thing must be done.

I solve it basing on this post mapping-to-oracle-stored-procedure-with-fluent-nhibernate

Here is my summary:

1. In the procedure you have to declare a first parameter of type OUT SYS_REFCURSOR. ex: p_cursor OUT SYS_REFCURSOR, MyVar1 int, MyVar2 nvarchar

2. To return the resulset do

   OPEN p_cursor FOR <select statement here>;
   

   in my case was a dynamic query and it works like a charm.

3. In the hbm mapping surround the call between

   { }
   

   ex:

    { call MyProc (MyVar1, MyVar2) }
   

   If you do not use the { }, nhibernate throws exception of "incorrect number of arguments".

I hope this helps someone.

I got the same trouble. I solved it by this:

Oracle PL/SQL in MyPackage:

...
function MyFunction(a number) return number is
n number;
pragma autonomous_transaction; -- Important!
begin
   n:=apps.fnd_request.submit_request( ... );
   commit; -- Important!
   return n;
end;

mapping.hbm.xml: (Embedded Resource)

  ...
  <sql-query name='MyFunctionQuery'>
    <query-param name='par' type='System.Int64'/>
    <return-scalar column='ret' type='System.Int64'/>
    <![CDATA[ 
      select MyPackage.MyFunction(:par) as ret from dual
    ]]>
  </sql-query>

C#>

 ...
 IQuery Q = session.GetNamedQuery("MyFunctionQuery")
                   .SetParameter("par", 1);
 var result = Q.UniqueResult();

Best regards.

With the same thing on my side and on Oracle, NHibernate seems to loose the name of the procedure inside the package. So let's suppose the example upfront: call MYPACKAGE.usp_GetOrders ( :pCompanyCode , :pOrderNumer ) --> result that the package MyPackage is not a stored procedure.

WARN: Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 7: PLS-00221: 'MYPACKAGE' is not a procedure or is undefined ORA-06550: line 1, column 7: