Archive for September, 2007

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

Tuesday, September 11th, 2007

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

Huh? If I remove the call to setMaxResults it works fine. I turn on SQL logging again and see that the autogenerated SQL to limit the result count for Oracle is

select * from ( { ? = call profile_attribute.lookup_hist_attr(?, ?) } ) where rownum <= ?

which of course is valid for regular SQL but garbage for a CallableStatement. I re-read the Hibernate documentation and it does say that "Stored procedure queries can't be paged with setFirstResult()/setMaxResults()" but you'd think that this would be enforced programmatically, rather than passing junk SQL down to the database and letting it fail there.

Bummer. So I remove the call to setMaxResults and add a TODO to fix this later, but when I go to run the tests in Ant before checking everything in they fail due to SQL comments. This is very weird and must be due to some misconfiguration in the test code, but even dumping the configuration settings during the test shows that all the properties are set to false.

At this point I've had enough, and realize that I'm working way too hard to get around Hibernate's limitations. I'm not playing the "Hibernate game" though - using HQL in a database-independent fashion and using ORM to manage objects conveniently. This is a very Oracle-specific piece of functionality, and so finally I realize that I should just use straight JDBC (or more accurately Spring's JdbcTemplate).

A couple of hours of refactoring later, all is well and although I needed to write more code for the JDBC approach than the Hibernate approach, it's more direct and less hackish.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.