Results 1 to 4 of 4
  1. #1
    bernidupont is offline Member
    Join Date
    Apr 2014
    Posts
    1
    Rep Power
    0

    Default Error ORA-01795 "maximum number of expressions in a list is 1000"

    Hi the team,

    I'm facing a problem which cannot find its solution.
    I have a query which must contain a big set of values, greater than 100.

    With Java and Hibernate, if i want to filter my value, i have to do :

    Java Code:
    criteria.add(Restrictions.in(criteriaName, listOfValues);
    But i get the error code ORA-01795 "maximum number of expressions in a list is 1000".
    That's why i would like to make sub queries using the following code:
    Java Code:
      int maxValues = 1;
        int nbValues = listOfValues.size();
     
        // Determine the number of sub lists to be created.
        int nbCriteria = (int) (nbValues / maxValues);
        nbCriteria = (nbCriteria * maxValues == nbValues) ? nbCriteria : nbCriteria + 1;
     
        // Create the sub lists related to the criteria. 
        int startIndex = 0, stopIndex = startIndex + maxValues;
        for (int i = 0; i < nbCriteria; i++) {      
          if (stopIndex > nbValues) {
            stopIndex = nbValues;
          }    
          if (i == 0) {
            criteria.add(Restrictions.in(criteriaName, listOfValues.subList(startIndex, stopIndex)));        
          } else {
            criteria.add(Restrictions.or(Restrictions.in(criteriaName, listOfValues.subList(startIndex, stopIndex))));
          }
          startIndex += maxValues;   
          stopIndex += maxValues;
        }
    Normally Hibernate should generate a query like :
    Java Code:
    ...where criteriaName in (val1, ..., val100) OR criteriaName in (val101, ..., val200) ...)
    But Hibernate replaces the key word OR by AND. And i got:
    Java Code:
    ...where criteriaName in (val1, ..., val100) AND criteriaName in (val101, ..., val200) ...)
    Can you please help me to find a solution ?

    Regards,
    berni.
    Last edited by bernidupont; 04-14-2014 at 11:38 PM.

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,931
    Rep Power
    19

    Default Re: Error ORA-01795 "maximum number of expressions in a list is 1000"

    The general advice (for Oracle at least) has been to populate some pre-existing TEMP table then select from that.

    See Ask Tom from about a decade ago. But since you're using Hibernate that probably won't work for you.

    Anyway, the 'or' Restriction "OR's" together two Criterion. It doesn't OR the previous one with the new one.

    You want to build your Criterion in a separate method and then add that single Criterion (covering your entire WHERE clause) into the criteria.

    By adding two criterion to the criteria Hibernate simply defaults to AND.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    Join Date
    Apr 2014
    Posts
    47
    Rep Power
    0

    Default Re: Error ORA-01795 "maximum number of expressions in a list is 1000"


  4. #4
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,931
    Rep Power
    19

    Default Re: Error ORA-01795 "maximum number of expressions in a list is 1000"

    How is that any different to what I posted above?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

Similar Threads

  1. Replies: 3
    Last Post: 06-28-2013, 08:11 AM
  2. Replies: 1
    Last Post: 11-02-2012, 11:38 PM
  3. Replies: 2
    Last Post: 04-23-2012, 03:08 PM
  4. problem with argument list and precedence "(" and ")"
    By helpisontheway in forum Advanced Java
    Replies: 6
    Last Post: 12-24-2009, 07:50 AM
  5. "Error in number, try again."
    By finlandssvensk in forum New To Java
    Replies: 1
    Last Post: 11-26-2007, 10:36 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •