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
我在Oracle数据库中有一个PL / SQL函数,我无法更改。此函数采用标识实体的参数,创建该实体的副本,然后返回副本的ID。这看起来像
FUNCTION copy_entity(id IN NUMBER) RETURN NUMBER
功能copy_entity(id IN NUMBER)返回编号
I need to call this function from Hibernate. I tried creating a named SQL query with something similar to
我需要从Hibernate调用这个函数。我尝试使用类似的东西创建一个命名的SQL查询
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
作为查询,但从这个我似乎无法获得函数的返回值。 Hibernate的“返回标量”和类似的选项需要返回一个列名,而我没有列名。这引导我
SELECT copy_entity(:id) AS newEntityId
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.
使用newEntityId作为列名称使用“return-scalar”,但是这也没有用,因为Oracle会抛出一个异常,我不能在SELECT中调用INSERT(保存副本)。
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.
有没有办法获得这样的PL / SQL函数的返回值?该功能实际上要复杂得多,并且在应用程序的其他部分仍然需要,因此重写它并不是一个真正的选择。
2 个解决方案
#1
I hope/think you can use an anonymous PL/SQL block:
我希望/您认为可以使用匿名PL / SQL块:
begin :myresult = copy_entity(:id); end;
begin:myresult = copy_entity(:id);结束;
Now you have 'column name' myresult with the result.
现在你的结果是'列名'myresult。
I've never used hibernate so I hope it works. I don't know how flexible Hibernate is.
我从来没有使用过hibernate所以我希望它有效。我不知道Hibernate有多灵活。
#2
I think you are stuck using straight JDBC. The Hibernate documentation has this in the limitations section for Oracle:
我认为你坚持使用直接JDBC。 Hibernate文档在Oracle的限制部分中有这个:
For Oracle the following rules apply:
对于Oracle,以下规则适用:
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.
函数必须返回结果集。过程的第一个参数必须是返回结果集的OUT。这是通过在Oracle 9或10中使用SYS_REFCURSOR类型来完成的。在Oracle中,您需要定义REF CURSOR类型。有关详细信息,请参阅Oracle文献。
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运气不佳,并且可能最好制作一个简单的JDBC CallableStatement。
#1
I hope/think you can use an anonymous PL/SQL block:
我希望/您认为可以使用匿名PL / SQL块:
begin :myresult = copy_entity(:id); end;
begin:myresult = copy_entity(:id);结束;
Now you have 'column name' myresult with the result.
现在你的结果是'列名'myresult。
I've never used hibernate so I hope it works. I don't know how flexible Hibernate is.
我从来没有使用过hibernate所以我希望它有效。我不知道Hibernate有多灵活。
#2
I think you are stuck using straight JDBC. The Hibernate documentation has this in the limitations section for Oracle:
我认为你坚持使用直接JDBC。 Hibernate文档在Oracle的限制部分中有这个:
For Oracle the following rules apply:
对于Oracle,以下规则适用:
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.
函数必须返回结果集。过程的第一个参数必须是返回结果集的OUT。这是通过在Oracle 9或10中使用SYS_REFCURSOR类型来完成的。在Oracle中,您需要定义REF CURSOR类型。有关详细信息,请参阅Oracle文献。
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运气不佳,并且可能最好制作一个简单的JDBC CallableStatement。