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.

No, this results in an ORA-14551 exception because it isn't allowed to insert or update from a SELECT. — Thomas Lötzer
– Thomas Lötzer, Commented Jul 1, 2009 at 14:07

Theo · Accepted Answer · 2009-07-01 16:06:07Z

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.

answered Jul 1, 2009 at 16:06

Theo

8194 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

anon Over a year ago

I think you're on the right track. In pure JDBC you'd have an out parameter to a callable statement with a ? to hold the returned value. Hibernate should allow something similiar.

anon · Accepted Answer · 2009-12-02 18:26:09Z

1

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.

answered Dec 2, 2009 at 18:26

anon

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related