nhibernate，在Oracle中调用返回sys refcursor的函数 [英] nhibernate, call function in Oracle which returns sys refcursor
问题描述
我试图用nhibernate调用一个函数（oracle），它返回的是ref cursor，但是我没有用hbm文件成功，任何人都可以用这个指导我。
如果我使它像< return class ... 那样我得到配置错误。
我试过 {？ =调用package.function（：a，：b，：c）作为double} 的结果，即使这样做也不起作用。
使用nHibernate调用ORACLE函数/过程时有一些限制。 对于Oracle，以下规则适用： 函数必须返回结果集。 因为我遇到同样的问题，所以我试着玩一点。 以下是包裹程序： HEAD： BODY： 这是我的映射文件： 这是我用来测试它的代码： 正如您所看到的，REF CURSOR必须是您程序中的第一个参数（ 如果您想要返回实体，事情会变得更复杂一些。 映射文件必须指定返回类型（class）： 您必须定义您的实体： 这是你实体的映射文件： / p> 这是我的ORACLE包： 现在您可以使用参数轻松获取您的订单： article 帮助我理解必须完成的事情。 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 I tried There are some limitations when calling ORACLE functions/procedures with nHibernate. 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: BODY: This my mapping file: and this is the code I've used to test it: As you can see the REF CURSOR must be the first parameter in your procedure ( If you want to return entities, things get a little bit more complicated. Your mapping file must specify the return type (class): You have to define your entity: and this is the mapping file for your entity: This is my ORACLE package: And now you can easily get your orders using parameters: This article helped me to understand how thing must be done. 这篇关于nhibernate，在Oracle中调用返回sys refcursor的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
如
过程的第一个参数必须是返回结果集的OUT。这是通过
在Oracle 9或10中使用SYS_REFCURSOR类型完成的。在Oracle中，您需要
定义REF CURSOR类型，请参阅Oracle文献。
创建或替换
PACKAGEMYPACKAGEAS
TYPE参考光标IS REF CURSOR;
PROCEDURE usp_GetDual
（
pCursor OUT ReferenceCursor，
a CHAR，
b CHAR
）;
END MYPACKAGE;
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;
例外
当其他
err_code：= SQLCODE;
err_msg：= substr（SQLERRM，1，200）;
END usp_GetDual;
<？xml version =1.0encoding =utf-8？>
{call MYPACKAGE.usp_GetDual（：a，：b）}
< / sql-query>
< / hibernate-mapping>
var value = Session.GetNamedQuery（GetDaul）
.SetParameter< string>（a，AAA）
。 SetParameter< string>（b，BBB）
.UniqueResult（）;
pCursor OUT ReferenceCursor ），您不需要在映射或调用中引用它。
<？xml version =1.0encoding =utf-8？>
< return class =MyAssembly.Domain.MyOrder，MyAssembly/>
{call MYPACKAGE.usp_GetOrders（：pCompanyCode，：pOrderNumer）}
< / sql-query>
< / hibernate-mapping>
public class MyOrder
{
public virtual string Number {get;组; }
public virtual int Ver {get;私人设置; }
public virtual string Company {get;组; }
公共虚拟字符串Customer {get;组; }
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
返回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）;
$ / code $ / pre
< hibernate-mapping xmlns =urn：nhibernate-mapping-2.2assembly = MyAssemblynamespace =MyAssembly.Domain>
< class name =MyOrdertable =OCSAORHmutable =false>
< composite-id>
< key-property name =Numbercolumn =OCHORDNtype =Stringlength =10>< / key-property>
< property name =Customercolumn =OCHCLIItype =String>< / property>
< / class>
< / hibernate-mapping>
<$ p
$ c $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ p $ $ b IS
err_code NUMBER;
err_msg VARCHAR2（200）;
BEGIN
OPEN pCursor FOR
SELECT
OCSAORH。*
OCSAORH
OCSAORH.OCHAMND = 0
AND OCSAORH.OCHCOSC = pCompanyCode
AND OCSAORH.OCHORDN = pOrderNumer;
EXCEPTION
当其他
err_code：= SQLCODE;
err_msg：= substr（SQLERRM，1，200）;
END usp_GetOrders;
var listOfOrders = Session.GetNamedQuery（GetOrder）
.SetParameter< string>（pCompanyCode，ABC）
.SetParameter<字符串>（pOrderNumer，XYZ）
.List< Domain.MyOrder>（）;
<return class ... I am getting configuration error.{ ? = call package.function(:a, :b, :c) as result from dual }, even this is also not working.
As stated in the reference documentation (17.2.2.1):
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;
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;
<?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>
var value = Session.GetNamedQuery("GetDaul")
.SetParameter<string>("a", "AAA")
.SetParameter<string>("b", "BBB")
.UniqueResult();
pCursor OUT ReferenceCursor) and you do not need to reference it in your mapping or your call. <?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>
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);
}
}
<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>
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;
var listOfOrders = Session.GetNamedQuery("GetOrder")
.SetParameter<string>("pCompanyCode", "ABC")
.SetParameter<string>("pOrderNumer", "XYZ")
.List<Domain.MyOrder>();
