umzzil nng

Posted on Aug 2 • Originally published at oraerror.com

Oracle ORA-01795 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01795: Maximum Number of Expressions in a List is 1000

ORA-01795 is a hard limit enforced by Oracle Database that prevents any single IN clause from containing more than 1,000 expressions (literals or bind variables). This error typically surfaces in production environments when data volumes grow beyond what was anticipated during development. It is one of the most common "works on my machine" bugs in Oracle-based applications.

Top 3 Causes

1. Dynamically Built IN Clauses in Application Code

The most frequent cause: application code (Java, Python, C#, etc.) collects a list of IDs and constructs a SQL string by joining them into an IN clause. It works fine in development with small datasets but blows up in production.

-- Problematic pattern (fails when list exceeds 1,000 items)
SELECT order_id, customer_name, total_amount
FROM   orders
WHERE  order_id IN (10001, 10002, 10003, /* ... 1,500 values total */);
-- ORA-01795: maximum number of expressions in a list is 1000

2. Two-Step Fetch Anti-Pattern

Instead of using a subquery or JOIN, some implementations fetch a list of IDs in the first query and then pass those IDs into a second query via an IN clause. This is a common ORM anti-pattern that breaks when result sets grow large.

-- Step 1 (application fetches IDs)
SELECT customer_id FROM customers WHERE region = 'WEST';
-- Returns 1,200 rows → application stores them in a list

-- Step 2 (application plugs IDs into IN clause — breaks!)
SELECT order_id, order_date
FROM   orders
WHERE  customer_id IN (/* 1,200 IDs from step 1 */);
-- ORA-01795 fires here

3. Batch Jobs Without Chunk Processing

Batch programs that collect a full set of processing keys and submit them in one query without splitting into smaller chunks will eventually trigger ORA-01795 as data grows.

-- Batch job without chunking (dangerous pattern)
UPDATE employee_payroll
SET    processed_flag = 'Y',
       process_date   = SYSDATE
WHERE  employee_id IN (/* entire pending list — could be thousands */);
-- Fails once list exceeds 1,000

Quick Fix Solutions

Fix 1: Split IN Clauses with OR

The fastest workaround — split the list into groups of up to 999 and combine with OR.

-- Split into two groups using OR
SELECT order_id, order_date
FROM   orders
WHERE  order_id IN (10001, 10002, /* ... up to 999 */)
    OR order_id IN (11000, 11001, /* ... remainder */);

Fix 2: Replace with a Subquery (Best Practice)

Eliminate the two-step fetch entirely by expressing the condition as a subquery within a single SQL statement.

-- Clean, scalable solution using a subquery
SELECT o.order_id, o.order_date, o.total_amount
FROM   orders o
WHERE  o.customer_id IN (
    SELECT c.customer_id
    FROM   customers c
    WHERE  c.region     = 'WEST'
      AND  c.status     = 'ACTIVE'
      AND  c.signup_date >= DATE '2022-01-01'
);

Fix 3: Use a Global Temporary Table

For large ID sets that cannot be expressed as a subquery, load them into a Global Temporary Table and JOIN.

-- Create GTT once
CREATE GLOBAL TEMPORARY TABLE tmp_target_ids (
    target_id NUMBER
) ON COMMIT DELETE ROWS;

-- Populate it
INSERT INTO tmp_target_ids SELECT COLUMN_VALUE
FROM TABLE(SYS.ODCINUMBERLIST(1001,1002,1003 /*, ... unlimited */));
COMMIT;

-- Use a JOIN instead of IN
SELECT e.employee_id, e.first_name, e.salary
FROM   employees    e
JOIN   tmp_target_ids t ON e.employee_id = t.target_id;

Fix 4: Use Oracle Collection Types (TABLE() function)

SYS.ODCINUMBERLIST and SYS.ODCIVARCHAR2LIST allow you to pass large collections into SQL without hitting the 1,000-item limit.

-- Works with more than 1,000 values
SELECT employee_id, last_name
FROM   employees
WHERE  employee_id IN (
    SELECT COLUMN_VALUE
    FROM   TABLE(SYS.ODCINUMBERLIST(
        1001,1002,1003, /* ... 2,000+ values allowed */
        2001,2002,2003
    ))
);

Prevention Tips

1. Enforce a "No Dynamic IN Clauses" Coding Standard
Add a rule to your team's SQL guidelines and code review checklist: any IN clause with a runtime-variable list must use a subquery, GTT, or collection type. Flag any code that concatenates SQL strings with comma-separated ID lists as a mandatory fix before merge.

2. Test with Production-Scale Data Volumes
ORA-01795 is invisible with small datasets. Mandate that integration and performance tests run against data volumes matching at least 70% of production scale. For batch jobs, explicitly document and test the maximum expected row count per execution cycle, and build in automatic chunking logic (e.g., process in blocks of 500) as a defensive measure.

Related Errors

ORA-00913 – too many values: triggers when too many values are supplied in a comparison or INSERT context.
ORA-00907 – missing right parenthesis: can appear when dynamically splitting IN clauses introduces a syntax error.
ORA-04031 – unable to allocate shared memory: oversized dynamic SQL statements from large IN lists can contribute to shared pool pressure.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community