« 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.

Posted by Daniel Rinehart on December 20, 2005 10:22 AM |

Comments

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.

Posted by: Anonymous | May 16, 2008 9:14 PM

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

Posted by: Anonymous | June 3, 2008 1:50 PM

Wow, your post is from 2005 and I still get the error now that it is 2011. Way to go Hibernate..

Posted by: moman | June 27, 2011 3:51 AM

try following workaround for this issue: 
        Integer intParam = userObj.getAge();
        if(intParam != null)
           query.setParameter("intParam", intParam);
        else
           query.setParameter("intParam", "");
for String/Date types, it did not throw the exception.

Posted by: Anonymous | July 28, 2011 12:47 PM