Oracle and Hibernate = annoyances

Spent most of the day working on what should have been an easy problem. From my current Java applaication I needed to call an Oracle Stored Procedure.

After a bit of digging I found that under Hibernate things aren’t as simple as they might seem – because you can’t just call any stored procedure – it has to be one that returns a result set.

Frome the Hibernate docs:

“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.”

Spent rather longer than I wanted to trying to shoehorn the SP into a structure that would return a result set, but in the end went back and rewrote the Java to use the underlying session via:

CallableStatement statement = session.connection().prepareCall(…);

The code to pick up the SP definition from the hibernate XML still seems clunky, in that at present I have to retrieve it as a query extract the SQL and then create a callable statement.

For the moment I’ll ignore the fact that connection is deprecated in Hibernate until they provide me something better.

Now that I know what the problem is there are some more elegant looking routes to the same point on Stack Overflow. Another item for the polishing list once I get all the core functionality working and tested.

Another further annoyance was down to my own stupiditiy – because my Stored Proc returned a value it had been written in Oracle as a Function rather than a Procedure. Much head scratching – Oracle was telling me it didn’t exist but in SQL Plus DESCRIBE was showing it as plain as day. I could call it through TOAD from my workspace but not directly or from J2EE

A salutory lesson in reading the info in front of me – Doh!

Advertisements

East Lothian dad. Likes bad jokes and comic songs. Mostly tired, tries to stay cheerful. Thinks space is awesome. Would like to write more code and better docs.

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: