nhibernate，在Oracle中调用返回sys refcursor的函数

问题描述

我试图用nhibernate调用一个函数（oracle），它返回的是ref cursor，但是我没有用hbm文件成功，任何人都可以用这个指导我。

如果我使它像< return class ... 那样我得到配置错误。

我试过 {？ =调用package.function（：a，：b，：c）作为double} 的结果，即使这样做也不起作用。

解决方案

使用nHibernate调用ORACLE函数/过程时有一些限制。

如帮助我理解必须完成的事情。

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):

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.

这篇关于nhibernate，在Oracle中调用返回sys refcursor的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！