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

hibernatejavaora-01795oracle

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!

Best Answer

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

Related Topic