Getting the return value of a PL/SQL function via Hibernate

Question

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.

Answer 1

I hope/think you can use an anonymous PL/SQL block:

begin :myresult = copy_entity(:id); end;

Now you have 'column name' myresult with the result.

I've never used hibernate so I hope it works. I don't know how flexible Hibernate is.

Answer 2

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.