« Sleep and Dreams Part 2 | Main | Sleep and Dreams Part 3 »

Hibernate, Oracle, Named Query, and Null

I'm sure this is captured in a bunch of FAQs but I thought I'd mention it here so I at least have an easy place to find it in the future. If you have a named query with parameters in Hibernate at some point you will probably run across this error:

Hibernate operation: could not execute query; bad SQL grammar [select table0_.id as id, table0_.version_stamp as version3_90_, table0_.user_id as user4_90_, table0_.confirmed as confirmed90_ from example_table table0_ where table0_.user_id=?]; nested exception is java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

While the error looks nasty, the most likely cause is that you accidentally passed in a Java null value instead of the expected type. In this case it should have been a Java Long value.


No, this is a killer Hibernate bug! If you have a member like:
private Boolean activeFlag;
and your name query is like:
... AND (NULL = :activeFlag OR EXISTS (...) ...
you will get this problem if you set your active flag with null.
The Hibernate bug id is: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2851 and it is still not fixed as of 3.2.5 One more reason to switch to TopLink, I had enough with showstopper Hibernate bugs ...
Wow, your post is from 2005 and I still get the error now that it is 2011. Way to go Hibernate..
try following workaround for this issue:
        Integer intParam = userObj.getAge();
        if(intParam != null)
           query.setParameter("intParam", intParam);
           query.setParameter("intParam", "");
for String/Date types, it did not throw the exception.