nhibernate，在 Oracle 中调用返回 sys refcursor 的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5901638/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
nhibernate, call function in Oracle which returns sys refcursor
提问by user740430
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.
我正在尝试使用 nhibernate 调用一个返回 ref 游标的函数（oracle），但是我没有成功使用 hbm 文件，任何人都可以指导我。
If i make it like <return class ...I am getting configuration error.
如果我让它像<return class ...我收到配置错误一样。
I tried { ? = call package.function(:a, :b, :c) as result from dual }, even this is also not working.
我试过了{ ? = call package.function(:a, :b, :c) as result from dual }，即使这也不起作用。
回答by LeftyX
There are some limitations when calling ORACLE functions/procedures with nHibernate.
As stated in the reference documentation(17.2.2.1):
使用 nHibernate 调用 ORACLE 函数/过程时有一些限制。
如参考文档(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.
对于 Oracle，以下规则适用：
一个函数必须返回一个结果集。过程的第一个参数必须是返回结果集的 OUT。这是通过在 Oracle 9 或 10 中使用 SYS_REFCURSOR 类型来完成的。在 Oracle 中，您需要定义一个 REF CURSOR 类型，请参阅 Oracle 文献。
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.
如您所见，REF CURSOR 必须是您的过程 ( pCursor OUT ReferenceCursor) 中的第一个参数，您无需在映射或调用中引用它。
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:
这是我的 ORACLE 包：
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 articlehelped me to understand how thing must be done.
这篇文章帮助我了解了必须如何做。
回答by Andres
I solve it basing on this post mapping-to-oracle-stored-procedure-with-fluent-nhibernate
我根据这个 post mapping-to-oracle-stored-procedure-with-fluent-nhibernate解决了它
Here is my summary:
这是我的总结：
- 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 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.
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".
- 在该过程中，您必须声明类型为的第一个参数
OUT SYS_REFCURSOR。前任：p_cursor OUT SYS_REFCURSOR, MyVar1 int, MyVar2 nvarchar 返回结果集
OPEN p_cursor FOR <select statement here>;在我的例子中是一个动态查询，它就像一个魅力。
在 hbm 映射环绕之间的调用
{ }前任：
{ call MyProc (MyVar1, MyVar2) }如果不使用
{ }，nhibernate 会抛出“参数数量不正确”的异常。
I hope this helps someone.
我希望这可以帮助别人。
回答by user1347098
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 MyPackageis not a stored procedure.
在我这边和 Oracle 上有同样的事情，NHibernate 似乎松散了包内的过程的名称。因此，让我们预先假设示例：call MYPACKAGE.usp_GetOrders ( :pCompanyCode , :pOrderNumer )--> 结果包MyPackage不是存储过程。
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:
警告：Oracle.DataAccess.Client.OracleException ORA-06550：第 1 行，第 7 列：PLS-00221：“MYPACKAGE”不是过程或未定义 ORA-06550：第 1 行，第 7 列：
回答by PetrCZ
I got the same trouble. I solved it by this:
我遇到了同样的麻烦。我是这样解决的：
Oracle PL/SQL in MyPackage:
MyPackage 中的 Oracle PL/SQL：
...
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)
mapping.hbm.xml: (嵌入式资源)
...
<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#>
C#>
...
IQuery Q = session.GetNamedQuery("MyFunctionQuery")
.SetParameter("par", 1);
var result = Q.UniqueResult();
Best regards.
此致。
