ORA-01795: Maximum 1000 Expressions in IN List - Workarounds

ORA-01795: Maximum of 1000 Expressions in a List

Error Overview

Error Text: ORA-01795: maximum number of expressions in a list is 1000

The ORA-01795 error is raised when an IN list in a SQL WHERE clause contains more than 1000 literal values. This is a hard architectural limit in Oracle’s SQL parser and applies to all forms of column IN (val1, val2, ..., valN) where N exceeds 1000. The error occurs at parse time, so no data is read or modified. It commonly surfaces in applications that build dynamic IN lists from large arrays, batch processing code, or report tools that pass hundreds or thousands of IDs to a query.

Common Causes

1. Application-Generated IN Lists

• Batch processing code collecting thousands of IDs and passing them as a literal IN list
• Report tools or dashboards selecting from large user-chosen value sets
• Nightly ETL jobs filtering on large sets of reference keys

2. ORM or Framework Behavior

• Hibernate session.get() with a large collection generating an IN clause
• Spring Data JPA findAllById() called with a list of thousands of IDs
• ActiveRecord or similar ORMs not chunking large where id in (...) collections

3. Dynamic SQL Construction

• Application code building SQL strings via string concatenation without limit checks
• Stored procedures receiving large VARCHAR2 comma-delimited lists
• APEX or BI tools passing large multi-select filter values

4. Data Migration and Bulk Operations

• Migration scripts selecting source rows by a large set of legacy IDs
• One-off cleanup scripts provided with a full list of affected rows

Diagnostic Queries

Find Long IN-List Queries in Shared Pool

-- Identify queries with very long IN lists in the shared pool
SELECT
  sql_id,
  executions,
  parse_calls,
  SUBSTR(sql_text, 1, 200) AS sql_preview,
  LENGTH(sql_text) AS sql_length
FROM v$sql
WHERE sql_text LIKE '%IN (%'
  AND LENGTH(sql_text) > 5000
ORDER BY sql_length DESC
FETCH FIRST 20 ROWS ONLY;

-- Check historical occurrences
SELECT
  sql_id,
  SUM(executions_delta) AS executions,
  SUM(parse_calls_delta) AS parses,
  MIN(begin_interval_time) AS first_seen,
  MAX(end_interval_time) AS last_seen
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot sn USING (snap_id, dbid, instance_number)
WHERE sql_id IN (
  SELECT sql_id FROM dba_hist_sqltext
  WHERE sql_text LIKE '%IN (%'
  AND DBMS_LOB.GETLENGTH(sql_text) > 5000
)
GROUP BY sql_id
ORDER BY executions DESC;

Count Items in a Dynamically Built List

-- Helper: count elements in a comma-separated string
SELECT
  REGEXP_COUNT('val1,val2,val3,...', ',') + 1 AS element_count
FROM dual;

-- Check if a delimited string exceeds 1000 elements
SELECT
  CASE
    WHEN REGEXP_COUNT(v_list, ',') + 1 > 1000 THEN 'EXCEEDS LIMIT'
    ELSE 'OK'
  END AS status
FROM dual;

Step-by-Step Resolution

1. Replace IN List with a Subquery (Best Long-Term Fix)

Instead of passing literal values, store them in a table and join:

-- BAD: literal IN list over 1000 items
SELECT * FROM orders
WHERE order_id IN (1001, 1002, 1003, ... /* >1000 values */);

-- GOOD: use a subquery against a staging or temporary table
-- First, populate a global temporary table or collection
INSERT INTO tmp_id_list (id) VALUES (1001);
INSERT INTO tmp_id_list (id) VALUES (1002);
-- ... bulk insert all IDs
COMMIT;

SELECT o.*
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM tmp_id_list t WHERE t.id = o.order_id
);

-- Or use a JOIN
SELECT o.*
FROM orders o
JOIN tmp_id_list t ON t.id = o.order_id;

2. Use a Global Temporary Table

-- Create a reusable GTT for ID lists
CREATE GLOBAL TEMPORARY TABLE tmp_id_list (
  id NUMBER
) ON COMMIT DELETE ROWS;

-- Populate and query
BEGIN
  -- Bulk insert the IDs
  INSERT INTO tmp_id_list
  SELECT COLUMN_VALUE FROM TABLE(your_id_collection);

  -- Use in query
  FOR rec IN (
    SELECT o.*
    FROM orders o
    WHERE o.order_id IN (SELECT id FROM tmp_id_list)
  ) LOOP
    process_order(rec);
  END LOOP;
END;
/

3. Split the IN List into Chunks of ≤1000

When the query must use IN lists (e.g., legacy code), split into multiple queries combined with UNION ALL:

-- Split approach: each IN list has ≤ 1000 items
SELECT * FROM orders WHERE order_id IN (/* items 1-1000 */)
UNION ALL
SELECT * FROM orders WHERE order_id IN (/* items 1001-2000 */)
UNION ALL
SELECT * FROM orders WHERE order_id IN (/* items 2001-3000 */);

In PL/SQL, automate the chunking:

CREATE OR REPLACE PROCEDURE query_by_ids(p_ids SYS.ODCINUMBERLIST) AS
  TYPE t_results IS TABLE OF orders%ROWTYPE;
  v_results t_results;
  v_chunk   SYS.ODCINUMBERLIST;
  v_start   PLS_INTEGER := 1;
  v_end     PLS_INTEGER;
  v_size    CONSTANT PLS_INTEGER := 999;
BEGIN
  WHILE v_start <= p_ids.COUNT LOOP
    v_end := LEAST(v_start + v_size - 1, p_ids.COUNT);
    v_chunk := SYS.ODCINUMBERLIST();

    FOR i IN v_start..v_end LOOP
      v_chunk.EXTEND;
      v_chunk(v_chunk.COUNT) := p_ids(i);
    END LOOP;

    SELECT * BULK COLLECT INTO v_results
    FROM orders
    WHERE order_id IN (SELECT COLUMN_VALUE FROM TABLE(v_chunk));

    -- Process v_results ...
    v_start := v_end + 1;
  END LOOP;
END;
/

4. Use a Collection with TABLE() Operator

Oracle’s TABLE() function lets you pass a PL/SQL collection as a set to query against, bypassing the 1000-item limit entirely:

-- Define a collection type (or use built-in SYS.ODCINUMBERLIST)
DECLARE
  v_ids SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(
    1001, 1002, 1003  -- add as many as needed
  );
BEGIN
  FOR rec IN (
    SELECT o.*
    FROM orders o
    WHERE o.order_id IN (
      SELECT COLUMN_VALUE FROM TABLE(v_ids)
    )
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.order_id);
  END LOOP;
END;
/

5. Use a WITH Clause (CTE) for Inline Value Lists

For moderate overages just above 1000, a CTE can hold the values without a physical table:

WITH id_list (id) AS (
  SELECT 1001 FROM dual UNION ALL
  SELECT 1002 FROM dual UNION ALL
  SELECT 1003 FROM dual
  -- ... up to any number
)
SELECT o.*
FROM orders o
JOIN id_list il ON il.id = o.order_id;

Prevention Strategies

1. Enforce Chunking in Application Code

-- Application-side pseudocode pattern (PL/SQL example)
CREATE OR REPLACE FUNCTION build_in_clause(
  p_ids      IN SYS.ODCINUMBERLIST,
  p_max_size IN PLS_INTEGER DEFAULT 999
) RETURN VARCHAR2 AS
  v_clause VARCHAR2(32767) := '';
  v_count  PLS_INTEGER := 0;
BEGIN
  IF p_ids.COUNT > p_max_size THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Use TABLE() operator for lists over ' || p_max_size || ' items.');
  END IF;

  FOR i IN 1..p_ids.COUNT LOOP
    v_clause := v_clause || p_ids(i);
    IF i < p_ids.COUNT THEN v_clause := v_clause || ','; END IF;
  END LOOP;

  RETURN v_clause;
END;
/

2. Prefer Set-Based Queries Over IN Lists

• Design batch processes to operate on ranges (BETWEEN) or status columns rather than explicit ID lists
• Use partitioned tables or index-organized tables to enable efficient range scans
• Use Oracle’s built-in bulk collect and FORALL for batch DML instead of large IN lists

3. Validate Input Size at Application Layer

• Add a guard in any service method that builds an Oracle IN list
• Log a warning when input collections exceed 500 items and use the TABLE() path automatically
• Add integration tests with >1000 IDs to catch regressions before production

4. Use Bind Variables with Collections

-- Bind a collection as a variable rather than interpolating literals
VARIABLE v_result REFCURSOR
DECLARE
  v_ids SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST(/* populate */);
BEGIN
  OPEN :v_result FOR
    SELECT * FROM orders
    WHERE order_id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));
END;
/
PRINT v_result;

Related Errors

• ORA-00907 - Missing right parenthesis (malformed IN list)
• ORA-00936 - Missing expression
• ORA-01008 - Not all variables bound
• ORA-04030 - Out of process memory (large collections)