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!

ORA-01795 is an Oracle error code, which would suggest that it's an Oracle, rather than Hibernate, limitation. — Anthony Grist, Mar 13 '12 at 10:58

score 8 · Accepted Answer · answered Mar 13 '12 at 10:58

8

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

answered Mar 13 '12 at 10:58

Jon Skeet

1,421,763
867
9,128
9,194

+1 for the workaround :) In addition: A quick google search reveals numerous posts of ppl with the same problem - so there might be already some approaches on overcoming this restriction... – quaylar Mar 13 '12 at 11:01

Fahim Ashraf · Answer 2 · 2017-10-15T14:32:03.987

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.

score 0 · Answer 3 · answered Sep 05 '13 at 08:26

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);
  }

score 0 · Answer 4 · answered Nov 05 '19 at 08:50

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

score 0 · Answer 5 · answered Mar 13 '12 at 10:58

0

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

answered Mar 13 '12 at 10:58

quaylar

2,617
1
17
31

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

5 Answers5

Linked