SQL IN list builder
Paste a column of values and get a quoted, escaped IN (…) clause — apostrophes, backslashes and leading zeros included.
Paste your values
One per line, or comma or tab separated.
1 of 6 lines contain commas, so the commas may be part of the values. Check before generating.
Your IN clause
6 values
- 1 value starts with a zero (e.g. "007"), so it is quoted as strings and the zeros are preserved.
How to Bypass Oracle ORA-01795: Maximum Number of Expressions in a List is 1000
Anyone migrating data, auditing financial records, or running ad-hoc queries on Oracle Database has encountered this classic error:
The Hard Limit
Oracle SQL imposes a hard parser restriction allowing a maximum of 1,000 values inside an IN (...) clause. A 1,001st value halts query execution immediately.
The Chunker Fix
The standard fix wraps values in parentheses connected by OR: WHERE (id IN (1..1000) OR id IN (1001..2000)). SQLParity automates this instantly.
100% In-Browser
Paste customer IDs, social security numbers, or transaction hashes directly from Excel. Zero data leaves your browser RAM.
Comparing the 3 Ways to Handle >1,000 Values in Oracle
WHERE (customer_id IN ('C001', 'C002', /* ...up to 1,000 items... */)
OR customer_id IN ('C1001', 'C1002', /* ...up to 2,000 items... */))Best for: Ad-hoc queries, scripts, and quick data triage. Requires no DDL privileges or temporary table creation.
WHERE (customer_id, 0) IN (('C001', 0), ('C002', 0), ...)Best for: Older scripts where parentheses cannot be rewritten easily. Oracle treats multi-column tuples as a single composite expression and bypasses the 1,000 literal limit.
CREATE GLOBAL TEMPORARY TABLE temp_ids (id VARCHAR2(50)) ON COMMIT PRESERVE ROWS; -- insert IDs and join SELECT * FROM sales s JOIN temp_ids t ON s.customer_id = t.id;
Best for: Production ETL jobs handling 50,000+ items where parsing huge SQL strings adds overhead to Oracle's shared pool.
Frequently Asked Questions
What causes Oracle error ORA-01795: maximum number of expressions in a list is 1000?
Oracle Database has a hardcoded parser limit of 1,000 literal elements in a single comma-separated IN list (e.g. WHERE id IN (1, 2, ..., 1001)). If your query passes 1,001 or more expressions, Oracle immediately rejects the query with ORA-01795.
How does SQLParity split large lists to avoid ORA-01795?
SQLParity groups your values into batches of 1,000 and connects them with OR operators: WHERE (id IN (1..1000) OR id IN (1001..2000)). Oracle evaluates each chunk as an independent list, executing the query normally.
What other workarounds exist for the Oracle 1000 IN list limit?
Common alternatives include: 1) The composite tuple workaround WHERE (id, 0) IN ((val1, 0), (val2, 0)...), which Oracle permits beyond 1,000 items; 2) Inserting items into a Global Temporary Table (GTT) and using an INNER JOIN or subquery; 3) Using XMLTABLE or JSON_TABLE arrays.
Does splitting into multiple OR clauses affect Oracle query performance?
For indexed columns, the Oracle Cost-Based Optimizer (CBO) handles chained OR IN clauses by executing an index range scan (CONCATENATION or INLIST ITERATOR) efficiently. However, for extremely large lists (>20,000 items), loading into a temporary table is recommended.