Oracle 存储过程、SYS_REFCURSOR 和 NHibernate
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/635848/
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
Oracle stored procedures, SYS_REFCURSOR and NHibernate
提问by Richard Nienaber
I have a legacy Oracle (10.2g) database that I'm connecting to and I'd like to use NHibernate (2.0.1) to give me back objects from a stored procedure. The stored procedure in question uses a SYS_REFCURSOR to return results. According to the documentationthis should be doable but I've found a fewpostson the internet that suggest otherwise.
我有一个要连接的旧版 Oracle (10.2g) 数据库，我想使用 NHibernate (2.0.1) 从存储过程返回对象。有问题的存储过程使用 SYS_REFCURSOR 来返回结果。根据该文件，这应该是可行的，但我已经发现了几个帖子暗示，否则在互联网上。
Here's my paraphrased code:
这是我的释义代码：
Mapping file:
映射文件：
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="OracleStoredProcedures" namespace="OracleStoredProcedures">
<class name="Person" mutable="false">
<id name="PersonCode" type="AnsiString" column="PERSONCODE">
<generator class="assigned" />
</id>
<property name="Name" type="String" column="PERSON_NAME" />
<property name="Surname" type="String" column="PERSON_SURNAME" />
</class>
<sql-query name="getpeople">
<return class="Person" />
EXEC RS_DB.GETPERSONTEST
</sql-query>
</hibernate-mapping>
Stored procedure:
存储过程：
CREATE OR REPLACE PROCEDURE RS_DB.GETPERSONTEST (
io_cursor IN OUT sys_refcursor
)
IS
BEGIN
OPEN io_cursor FOR
SELECT PERSONCODE, PERSON_NAME, PERSON_SURNAME
FROM PEOPLE
END GETPERSONTEST;
采纳答案by Sebastian Piu
As far as I remember this was a bug I also found a couple of years ago when working with oracle, I've tracked back the issue in NH tracker and is fixed but on version 2.1.1GA; Can you verify that this is the same problem you have? https://nhibernate.jira.com/browse/NH-847
据我所知，这是我几年前在使用 oracle 时也发现的一个错误，我已经在 NH 跟踪器中追溯了该问题并已修复，但在 2.1.1GA 版本上；你能确认这是你遇到的同样问题吗？ https://nhibernate.jira.com/browse/NH-847
回答by Deepfreezed
What a royal pain this was. This finally worked. I turned the store procedure into a function. Function returned sys_refcursor. Used similar mapping as the OP and name query as below.
这是何等的皇家痛苦。这终于奏效了。我把存储过程变成了一个函数。函数返回 sys_refcursor。使用与 OP 和名称查询类似的映射，如下所示。
<sql-query name="getpeople">
<return class="Person" />
{ ? = call RS_DB.GETPERSONTEST }
</sql-query>
回答by bananafish2k
I came across this same problem today. For us, the fix was to use "CALL" rather than "EXEC", add the round brackets "()" to the procedure call, and wrap the call in curly braces "{}":
我今天遇到了同样的问题。对我们来说，修复方法是使用“CALL”而不是“EXEC”，将圆括号“()”添加到过程调用中，并将调用包裹在花括号“{}”中：
<sql-query name="getpeople">
<return class="Person" />
{ CALL RS_DB.GETPERSONTEST() }
</sql-query>
回答by Rob M
回答by Adam Hawkes
In your hibernate you state a return type, but Oracle procedures don't return anything. Perhaps if you changed it to a function which returned the ref cursor it would work properly. Also, I believe CALL is the proper syntax. EXEC is a SQL*Plus command and not really a SQL statement.
在您的休眠状态中，您声明了返回类型，但 Oracle 过程不返回任何内容。也许如果您将其更改为返回引用游标的函数，它将正常工作。另外，我相信 CALL 是正确的语法。EXEC 是一个 SQL*Plus 命令，而不是真正的 SQL 语句。
