ORA-01795: maximum number of expressions in a list is 1000

I got Following error while fetching data from Oracle 10g using hibernate:

[CAR] [2010-02-14 23:59:02,994] WARN [QuartzScheduler_Worker-4] JDBCExceptionReporter.logExceptions(77) | SQL Error: 1795, SQLState: 42000
[CAR] [2010-02-14 23:59:02,994] ERROR [QuartzScheduler_Worker-4] JDBCExceptionReporter.logExceptions(78) | ORA-01795: maximum number of expressions
in a list is 1000

[CAR] [2010-02-14 23:59:02,996] ERROR [QuartzScheduler_Worker-4] TestManagerImpl.processFile(61) | - reading XML: failure
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; nested exception is org.hibernate.exception.SQLGrammarExce
ption: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:613)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:378)
at at java.lang.reflect.Method.invoke(Method.java:612)
at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:283)
at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactory
Bean.java:272)
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
at org.quartz.core.JobRunShell.run(JobRunShell.java:195)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:520)
Caused by:
java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:74)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:171)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:785)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:860)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1202)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
at org.hibernate.loader.Loader.doQuery(Loader.java:674)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 39 more

Workaround:

- When working with Oracle database, ORA-01795 (maximum number of expressions in a list) is a typical error when working with a large number of expressions in list parameters (IN(expr_list)).
- The workaround would be to use a sub-query
- eg:
select * from employee where emp_id in (1,2,3, ... 3000)
partition this parameter list in smaller lists with a maximum of 1000 elements (the oracle limit)