Caused by: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000?

Question

I am using java/hibernate/Oracle. i have a list with more than 3000 entries. if i pass whole list i get below exception.

Caused by: java.sql.SQLException: ORA-01795: maximum number of expressions in a list is 1000

to solve the issue i am splitting the list into sublists, each sublist will have 1000 entries. for every thousand entries i am firing a query. it is working fine.

Please clarify me, is there any better solution?

Thanks!

Answer 1

It's an Oracle limitation, which is why it's got an Oracle error code... although you could argue that it's a limitation of Hibernate that it doesn't transparently work around it :)

You should probably put the list into a temporary table and join on that, assuming Oracle doesn't have anything like SQL Server's table-valued parameters. (Or you could break your query up into multiple queries, potentailly - it depends on what you're doing.)