CODEWITHSUNDEEP
javaoracle-databasehibernateora-01795
user1016403
user1016403

Reputation: 12621

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

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!

Upvotes: 5

Views: 11240

Answers (5)

Neha
Neha

Reputation: 1

i have used below and it works perfectly for me

if (myArgList.size() > 1000) {
      while(myArgList.size()>0)
      {  
        tempList = myArgList;
        def listSize= tempList.size()
        if(listSize>1000){
            def subList = tempList.subList(0, 1000);
            def tcriteria = tableA.createCriteria()
            result.addAll(tcriteria.listDistinct {
                'in'('id', subList.collect{it.id})
                fetchMode('tableBObj', FetchMode.JOIN)
            })
            myArgList.subList(0, 1000).clear();
        }else{
            def subList = myArgList.subList(0, listSize);
            def ncriteria = tableA.createCriteria()
            result.addAll(ncriteria.listDistinct {
                'in'('id', subList.collect{it.id})
                fetchMode('tableBObj', FetchMode.JOIN)
            })                      
            myArgList.clear();
      }
    }
} else {
    def criteria = tableA.createCriteria()
    result = criteria.listDistinct {
        'in'('id', myArgList.collect{it.id})
        fetchMode('tableBObj', FetchMode.JOIN)
    }
}

Upvotes: 0

Fahim Ashraf
Fahim Ashraf

Reputation: 93

there is also another way to resolve this issue. lets say you have two tables Table1 and Table2. and it is required to fetch all entries of Table1 not referred/present in Table2 using Criteria query. So go ahead like this...

List list=new ArrayList(); 
Criteria cr=session.createCriteria(Table1.class);
cr.add(Restrictions.sqlRestriction("this_.id not in (select t2.t1_id from Table2 t2 )"));
.
.





List list=new ArrayList();
Criteria cr=session.createCriteria(Table1.class);
cr.add(Restrictions.sqlRestriction("this_.id not in (select t2.t1_id from Table2 t2 )"));
list=cr.list();
.
.
.

It will perform all the subquery finction directly in SQL without including 1000 or more parameters in SQL converted by Hibernate framework. It worked for me. Note: You may need to change SQL portion as per your requirement.

Upvotes: 0

Tuğşad Karaduman
Tuğşad Karaduman

Reputation: 141

You cant have a list with more than 1000 elements in a single "where" condition if you are working with Oracle DB. So you can chop down your "where" condition in multiple "where" conditions and join them with "or" clause.

If you are using hibernate Criteria, you can use below Java method to do this. Just replace your code where ever you used 

criteria.add(Restrictions.in(propertyName, mainList));

with 

addCriteriaIn(propertyName, mainList, criteria);

which the method is : 

 private void addCriteriaIn (String propertyName, List<?> list,Criteria criteria)
  {
    Disjunction or = Restrictions.disjunction();
    if(list.size()>1000)
    {        
      while(list.size()>1000)
      {
        List<?> subList = list.subList(0, 1000);
        or.add(Restrictions.in(propertyName, subList));
        list.subList(0, 1000).clear();
      }
    }
    or.add(Restrictions.in(propertyName, list));
    criteria.add(or);
  }

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1499790

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

Upvotes: 8

quaylar
quaylar

Reputation: 2635

The Exception-Text lets me believe that this is coming from the Oracle-Database - look at the error-number...

Upvotes: 0

Related Questions