Note to self: Don’t Use Stored Procs in Hibernate

We fired our DBA/Data Architect a few months ago and haven’t found a replacement yet, so I’ve been tasked with writing some PL/SQL. I’m a little rusty with PL/SQL and haven’t used Hibernate’s stored proc/function support before, so it’s been an interesting few days.

The first indication that it might not be totally straightforward was that Hibernate has a very specific format for calling procedures. The function must return a cursor as its first OUT parameter. That’s no big deal, I’m creating these from scratch, so there’s no need to adapt legacy code. Also, you can’t return an array; you must specify the entity class to be built from the results.

But then it gets weirder. I want to return a cursor that will work as a ResultSet that doesn’t map to an existing Hibernate-managed class. I’m using subclassing and won’t know which concrete class to instantiate until I get the result(s) back, but Hibernate needs to be configured as a Named Query with “callable=’true'” and with the concrete result class specified. I don’t want to create four different call types (and can’t anyway since I don’t know the return type) so I need to create a dummy entity class mapped to a dummy table to get Hibernate to deserialize the rows, then I can use that to create the actual instances via a discriminator column that I set in the stored function. Ok, no biggie, I’ll play along.

So I get everything basically working (the code’s ugly and has no error handling, but I’ll get to that …) and write some unit tests. Although the calls work fine in SQL Developer and SQL*Plus, all the tests fail:
53984 DEBUG JDBCExceptionReporter - could not execute query [{ ? = call profile_attribute.lookup_profile_attribute(?, ?)
}]
java.sql.SQLException: ORA-00900: invalid SQL statement


Huh. Looks fine to me. So I run the tests in the debugger and deep in the Hibernate code I see that Hibernate is appending a comment to the beginning of the SQL and when I turn on SQL logging I see that the SQL is actually:

/* named native SQL query getAttributeValue */ { ? = call profile_attribute.lookup_profile_attribute(?, ?) }


I have the properties hibernate.format_sql set to true, hibernate.use_sql_comments set to true, and hibernate.show_sql set to false so I can turn on SQL logging when things get weird – the formatting and comments help make sense of the generated SQL.

When I run the SQL with the prefixed comment in SQL*Plus, I get that same error. I searched for other people having experienced the same thing but didn’t find anything. So I reluctantly turned off hibernate.use_sql_comments and things worked fine. It would be convenient if this were being done in the Dialect since I could then subclass and only generate comments for non-CallableStatement calls, but it’s in the core code.

So then I modified the DAO code to limit the number of results – simple enough to do, just call Query.setMaxResults(). Nope:

java.sql.SQLException: ORA-00903: invalid table name