通过Hibernate获得PL / SQL函数的返回值 [英] Getting the return value of a PL/SQL function via Hibernate

问题描述

我在Oracle数据库中有一个我无法更改的PL / SQL函数。该函数接受一个标识实体的参数，创建该实体的副本，然后返回该副本的ID。这看起来像

FUNCTION copy_entity（id IN NUMBER）RETURN NUMBER

我需要从此函数调用休眠。我尝试用类似于

CALL copy_entity（：id）

作为查询来创建命名SQL查询，但从这我似乎无法得到函数的返回值。 Hibernate的返回标量和类似选项需要返回一个列名，而我没有列名。这导致我使用newEntityId使用return-scalar选择copy_entity（：id）AS newEntityId

作为列名，但这也不起作用，因为Oracle然后抛出一个异常，我不能调用INSERT（保存副本）在SELECT中。

任何获得这种PL / SQL函数的返回值的方法？这个函数实际上要复杂得多，而且在应用程序的其他部分仍然需要，所以重写它并不是一个真正的选择。 解决方案

我认为你被直接使用JDBC存在困难。 Hibernate文档在Oracle的限制部分有这个：

对于Oracle，以下规则适用：

函数必须返回结果集。
过程
的第一个参数必须是返回结果
set的OUT。这是通过在Oracle 9或10中使用
SYS_REFCURSOR类型完成的。
在Oracle中，您需要定义REF
CURSOR类型。有关
的更多信息，请参阅Oracle文献。

由于此函数接受一个数字并返回一个数字， Hibernate，可能会更好的做一个简单的JDBC CallableStatement 。

I have a PL/SQL function in an Oracle database that I can't change. This function takes a parameter which identifies an entity, creates a copy of that entity and then returns the ID of the copy. This looks something like

FUNCTION copy_entity(id IN NUMBER) RETURN NUMBER

I need to call this function from Hibernate. I tried creating a named SQL query with something similar to

CALL copy_entity(:id)

as the query, but from this I can't seem to get the return value of the function. Hibernate's "return-scalar" and similar options require a column name to return and I don't have a column name. This lead me to

SELECT copy_entity(:id) AS newEntityId

with "return-scalar" using newEntityId as column name, but this also did not work since Oracle then throws an exception that I can't call INSERT (to save the copy) in a SELECT.

Is there any way to get the return value of such a PL/SQL function? The function is actually much more complex and still required in other parts of the app, so re-writing it is not really an option.

解决方案

I think you are stuck using straight JDBC. The Hibernate documentation has this in the limitations section for Oracle:

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 for further information.

Since this function accepts a number and returns a number you are out of luck with Hibernate and would probably be better off making a simple JDBC CallableStatement.

这篇关于通过Hibernate获得PL / SQL函数的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！