ORA-01795: the 1000-expression limit on IN lists
Oracle allows at most 1000 expressions in a literal IN list. Paste 1200 ids from a spreadsheet and the query fails with ORA-01795 — not slowly, not partially, but immediately at parse time. The limit applies only to literal lists; IN (SELECT …) against a table or a collection has no such ceiling.
When you cannot use a subquery — a one-off investigation, a support ticket, ids that exist only in an email — the fix is to split the list and OR the parts together. This tool does the split, deduplicates, drops the trailing blank line every spreadsheet paste ends with, and quotes each value only when it needs quoting.
That last part matters more than it looks. A code like 007 is not the number 7: leave it unquoted and Oracle drops the leading zeros, the comparison silently fails, and the row you were looking for is simply absent from the result. Values that look numeric but start with a zero stay quoted.
Is splitting the list slower than one IN?
- Slightly, but the alternative is a query that does not run at all. If the list is large and recurring, load the ids into a global temporary table and join instead — that scales past any list length and lets the optimiser see a cardinality.
Does SQL Server have the same limit?
- No fixed limit of 1000, but very long IN lists hurt there too: each one produces a distinct query plan, filling the plan cache. Chunking is still worth doing above a few thousand values.