Hibernate Query Resulting in Missing Expression in Oracle

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

1 Like