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

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.

6 Responses to “Note to self: Don’t Use Stored Procs in Hibernate”

  1. Andy says:

    Actually the limitation is your own. You can use Hibernate AND JDBC:

    However PL/SQL is bad mojo because it moves the transaction demarcation generally to the DB which is generally not the best place for it as the “unit of work” is generally associated with your business logic….which then also has to be moved to PL/SQL….which sucks for this purpose really.


  2. If you want to write less code check out the new SimpleJdbcCall just introduced in Spring 2.1M4. There are some examples in ch. 11.5 of the reference doc. This requires Java 5 or better though.

  3. Burt says:

    Yeah, don’t get me started about how we’ve locked ourselves into being an Oracle-specific application – certainly not my choice.

    I intentionally left out the motivation behind this implementation. There is a bunch of work that needs to be done to determine what rows to return, so a stored proc does make sense here though.

  4. max says:


    Yes, Oracle stored procedures are the worst.

    Just to give you the info then:

    The comment issue is an oracle driver problem, comments works fine with e.g. DataDirect drivers.

    How can you not know what kind of entities that are going to be returned ? If *anything* can be returned this resultset is to be viewed as a class-hiearchy-per-table and should be mapped as such.

    Alternatively if you don’t need entities but just data objects returned used a resultTransformer and you can do it just like you probably did with springs jdbc template.

  5. igor says:

    Thanks for article , I have stacked with this
    comment thing for a week , changing everything
    that could be changed . I was getting NullPointerException from query.list().
    Now I realize , Oracle just ignore the code
    and send nothing in reply . Now all running

  6. Mitra Chan says:

    Thanks for the blog.
    It really helps me. I’m stucked with this thing for 2 weeks.

    And stucked for few days simply because of use_sql_comment. Your conclusion in the blog is the best. Gotcha.

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