We use Hibernate as ORM to map our entities to Database .
All our Database query / transactions happens through entity . The Queries for Database (select/insert/update/delete) are generated by hibernate .
In one scenario , we have added a criteria restriction (IN) to include a list of values as below .
c.add(Restrictions.in(column_name,list));
For certain business cases /scenario , the list can be empty . The above criteria will result hibernate to create a query as
select * from table where column_name in ();
This results in error as below in Oracle
Caused by: org.hibernate.exception.SQLGrammarException: ORA-00936: missing expression
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ~[hibernate-core-4.1.2.Final.jar:4.1.2.Final]
at com.sun.proxy.$Proxy1751.executeQuery(Unknown Source) ~[na:na]
In our development environment we use H2 and it supports empty IN Clause . We use oracle database in our QA environment and see above failure in QA .
Though it is clearly an oracle error message , I am trying to understand if there is any hibernate attribute/property that can be set to handle / manage this scenario by hibernate, while generating SQL.
Stack : hibernate-core-4.1.2,hibernate-entitymanager-4.1.2,hibernate-jpa-2.0-api-1.0.1.Final.jar
