Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-35334

Create oracle-specific version of get_in_or_equal() to handle limit of 1000 items



    • Improvement
    • Status: Closed
    • Minor
    • Resolution: Inactive
    • 2.3.2
    • None
    • Database SQL/XMLDB


      Oracle doesn't support more than 1000 items in an IN() clause - any more and the query fails with error ORA-01795.

      The problem is that often the list of items is generated dynamically, so most sites work fine but a big site will suddenly get breaking queries when it gets big enough.

      Back on 1.9 we had a function that we used when building an IN() clause from an array that would check the number of items and the database in use, and split the IN() for Oracle if there are too many items - changing this:

      IN (1,2,3,...,2009,2010)


      IN (1,2,3,...,999,1000) OR IN (1001,1002,...,1999,2000) OR IN (2001,...,2010)

      In Moodle 2, we use get_in_or_equal(), which could have the same issue - I was wondering if it would make sense to have an Oracle-specific version of get_in_or_equal() which did something similar?

      I'd be happy to write it (based off the code below) if it's something that would be wanted in core.

      Here's the code we were using (ported to 2.x):

       * Return the proper SQL to compare a field to multiple items
       * By default it uses IN but can be negated (to NOT IN) using the 3rd argument
       * The output from this is safe for Oracle, which has a limit of 1000 items in an
       * IN () call.
       * @param string $field The field to compare against
       * @param array $items Array of items. If text they must already be quoted.
       * @param boolean $negate Return code for NOT IN () instead of IN ()
       * @return array In the form array(sql, params) The SQL needed to compare $field to the items
       *              in $items and associated parameters
      function sql_sequence($field, $items, $type=SQL_PARAMS_QM, $negate = false) {
          global $DB;
          if (!is_array($items) || count($items) == 0) {
              return ($negate) ? array('1=1', array()) : array('1=0', array());
          $not = $negate ? 'NOT' : '';
          if ($DB->get_dbfamily() != 'oracle' || $count($items) <= 1000) {
              list($sql, $params) = $DB->get_in_or_equal($items, $type, 'param', !$negate);
              return array(" $field " . $sql, $params);
          $out = array();
          while ($some_items = totara_pop_n($items, 1000)) {
              list($sql, $params) = $DB->get_in_or_equal($items, $type, 'param', !$negate);
              $out[] =" $field " . $sql;
              $outparams = array_merge($outparams, $params);
          $operator = $negate ? ' AND ' : ' OR ';
          return array('(' . implode($operator, $out) . ')', $outparams);
       * Pop N items off the beginning of $items and return them as an array
       * @param array &$items Array of items (passed by reference)
       * @param integer $number Number of items to remove from the start of $items
       * @return array Array of $number items from $items, or false if $items is empty
      function totara_pop_n(&$items, $number) {
          if (count($items) == 0) {
              // none left, return false
              return false;
          } else if (count($items) < $number) {
              // return all remaining items
              $return = $items;
              $items = array();
              return $return;
          } else {
              // return the first N and shorten $items
              $return = array_slice($items, 0, $number, true);
              $items = array_slice($items, $number, null, true);
              return $return;


        Issue Links



              Unassigned Unassigned
              simoncoggins Simon Coggins
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              1 Vote for this issue
              3 Start watching this issue