Fix ORA-01795: Maximum Number of Expressions
More than 1,000 expressions in a single IN (…) or NOT IN (…) list triggers ORA-01795 on Oracle Database releases with that limit. Fix it by replacing the explicit list with a subquery or table-backed filter, or by splitting it into correctly connected smaller lists. The right choice depends on where the values originate and whether this is a one-off query or recurring application code.
Use the symptom to identify which part of the query needs attention:
| Symptom | Likely Cause | Best Next Action |
|---|---|---|
| A query fails only when the selected-ID list grows | One explicit list exceeds the database limit | Use a subquery, staged IDs, or smaller lists |
| Replacing literals with bind variables does not help | Bind placeholders still occupy list entries | Change the query structure, not just parameterization |
| A split query runs but returns incorrect rows | Incorrect AND/OR logic or missing parentheses |
Correct the Boolean grouping |
| A report or application fails without handwritten SQL | The tool generates an oversized list | Inspect its generated SQL and filter behavior |
What ORA-01795 Means
ORA-01795 is an expression-list error, not a limit on how many rows a table can contain or a query can return.
The common trigger is a predicate that places too many expressions directly inside parentheses. These expressions may be numeric literals, quoted strings, bind placeholders, or other scalar expressions. Repeating the same value does not make the written list shorter.
The restriction applies to each explicit list. It can affect filters inside nested queries and CASE expressions, not just the first WHERE clause you notice.
A critical distinction is a list of expressions versus rows returned by a query. An IN predicate containing a subquery does not become an oversized explicit list merely because that subquery returns many rows. That distinction makes a subquery one of the cleanest fixes.
The limit also depends on the database release. Confirm the exact ceiling in the Oracle Database error-message documentation and SQL Language Reference for the server release you actually use, not the version of your database client.
How to Fix ORA-01795
Step 1: Capture the SQL That Actually Fails
- Find the failing statement. In application code, inspect the ORM’s generated SQL or enable narrowly scoped SQL logging. In a reporting tool, capture the SQL generated by the failing report. Avoid putting sensitive parameter values into broadly accessible logs.
- Locate every explicit list. Check
INandNOT INpredicates, including those inside nested queries or conditional expressions. - Count the generated list entries. If the application builds the list, inspect its input collection and generated placeholders. Do not simply count every comma in the SQL: function calls and other expressions can contain commas too.
- Confirm the server release. Use your connection’s database information or run:
SELECT banner
FROM v$version;
If your account cannot query that view, ask the database administrator for the server release.
For a failing UPDATE or DELETE, first reproduce the filter with a SELECT so you can inspect the intended rows without modifying data.
Step 2: Choose the Smallest Correct Rewrite
When building an explicit scalar IN (...) predicate on releases with the older ceiling, use no more than 1,000 expressions per list, not 999, as documented in Oracle’s SQL performance guidance. A list exactly at the stated maximum is valid.
Choose the replacement based on the source of the IDs:
- IDs already stored in Oracle: Keep them in the database and use a subquery or
EXISTS. - A one-off list or application code that cannot create database objects: Split the list while preserving its Boolean logic.
- Large, recurring ID sets supplied by an application: Load the IDs into a precreated temporary or staging table, then query against that table.
For recurring workloads, a table-backed filter usually provides a more maintainable starting point than continually expanding SQL text. Its performance still needs testing.
Step 3: Apply the Chosen Rewrite
The examples below assume an orders table with an order_id column and, where used, a status column. Substitute your actual table and column names.
Use a Subquery for IDs Already in Oracle
If the desired IDs already exist in a table, do not retrieve them into the application only to paste them back into SQL.
For example, suppose selected_order_ids stores selections by request:
SELECT o.*
FROM orders o
WHERE o.order_id IN (
SELECT s.order_id
FROM selected_order_ids s
WHERE s.request_id = :request_id
);
The subquery can return more rows than the explicit-list ceiling because its results are rows, not individually written list expressions.
Bind the appropriate request ID, execute the statement, and verify that it returns only orders belonging to that selection. The request condition matters when a staging table holds selections for multiple users or jobs.
An IN subquery also avoids multiplying output rows when the selection table contains duplicate IDs. A plain inner join could introduce duplicates unless uniqueness is enforced.
Split an Explicit List With the Correct Operators
For an inclusion filter, join the smaller IN predicates with OR. Put the entire group in parentheses when it is combined with another condition.
This shortened example shows the structure:
SELECT o.*
FROM orders o
WHERE o.status = :status
AND (
o.order_id IN (:id1, :id2, :id3)
OR o.order_id IN (:id4, :id5, :id6)
);
Without the enclosing parentheses, SQL operator precedence can allow rows from a later list to bypass the status filter.
For an exclusion filter, join smaller NOT IN predicates with AND:
SELECT o.*
FROM orders o
WHERE o.order_id NOT IN (:id1, :id2, :id3)
AND o.order_id NOT IN (:id4, :id5, :id6);
Using OR for those exclusions changes the meaning: a row excluded by one list could still pass the other condition.
Also check for NULL in exclusion inputs. A NULL entry in a NOT IN list can make otherwise nonmatching comparisons evaluate to unknown, unexpectedly removing rows from the result. Define the intended null behavior before changing the predicate. A correlated NOT EXISTS filter is often clearer for table-backed exclusions, but its treatment of null outer values is not identical to NOT IN.
Use properly indented Python code when filtering values for bind variables, and avoid long IN lists.
def fetch_orders(connection, order_ids, status):
ids = list(dict.fromkeys(order_ids))
if any(order_id is None for order_id in ids):
raise ValueError("order_ids must not contain None")
if not ids:
return []
chunk_size = 1000
parameters = {"status": status}
predicates = []
for start in range(0, len(ids), chunk_size):
placeholders = []
for index, order_id in enumerate(
ids[start:start + chunk_size], start=start
):
name = f"id_{index}"
placeholders.append(f":{name}")
parameters[name] = order_id
predicates.append(
f"o.order_id IN ({', '.join(placeholders)})"
)
sql = (
"SELECT o.* FROM orders o "
"WHERE o.status = :status AND ("
+ " OR ".join(predicates)
+ ")"
)
with connection.cursor() as cursor:
cursor.execute(sql, parameters)
return cursor.fetchall()
Call it with your connection and actual ID collection:
rows = fetch_orders(connection, order_ids, "OPEN")
Only generated placeholder names are inserted into the SQL text; input values remain bound parameters. The empty-input branch deliberately returns no rows rather than generating invalid empty-list syntax.
Run the replacement with the selection that originally failed. Check both membership and any additional filters, and use an explicit ORDER BY if the application requires a particular row order.
Load External IDs Into a Temporary Table
For recurring external ID sets, a global temporary table separates the data from the SQL statement.
Have an authorized user create the table once during deployment:
CREATE GLOBAL TEMPORARY TABLE filter_order_ids (
order_id NUMBER NOT NULL
) ON COMMIT DELETE ROWS;
Use the same datatype as the target key if it is not numeric. Create this object in a separate setup session: Oracle DDL can commit an existing transaction, so do not create it in the middle of application work.
The table definition is shared, but each database session has its own data. The chosen ON COMMIT DELETE ROWS setting clears that session’s rows at transaction end.
To use it safely:
- Use a dedicated connection with no unrelated uncommitted work. Disable autocommit before loading the IDs, and keep that connection checked out through the final fetch.
- Clear any residual IDs on that connection:
DELETE FROM filter_order_ids;
- Load the actual IDs into filter_order_ids with bound inserts. Use your driver’s batch-binding facility, such as executemany, to execute this statement for each ID rather than constructing another comma-separated SQL list: “`sql INSERT INTO filter_order_ids (order_id) VALUES (:order_id); “`
INSERT INTO filter_order_ids (order_id)
VALUES (:order_id);
- Query the target table on the same connection:
SELECT o.*
FROM orders o
WHERE EXISTS (
SELECT 1
FROM filter_order_ids f
WHERE f.order_id = o.order_id
);
- Fetch and verify the query results, then commit the dedicated transaction to clear the temporary rows: “`sql COMMIT; “`
COMMIT;
- If loading filter_order_ids or querying orders fails, roll back the dedicated transaction before returning the connection to the pool: “`sql ROLLBACK; “`
ROLLBACK;
Do not commit between loading the IDs and executing the query: that would empty this temporary table. Do not load on one pooled connection and query on another, because their temporary rows are separate.
Using EXISTS prevents duplicate loaded IDs from duplicating the matching orders. After implementing the flow, rerun the original failing selection and verify both the returned IDs and the cleanup behavior on the reused connection.
Application and Reporting Tool Cases
ORMs, reports, and packaged applications require generated SQL inspection and the applicable supported rewrite, filter, or patch.
ORM-Generated SQL
An ORM can expand a collection into an explicit list even when your application code contains no visible SQL. A collection parameter is not necessarily sent to Oracle as a database collection; it may become a long series of scalar placeholders.
Inspect the SQL generated by the actual framework version and Oracle dialect. Some combinations split large lists automatically, while others require an application-level rewrite.
For a durable repair:
- Keep related IDs in a database query when possible.
- Configure the correct Oracle dialect for the server release.
- Test the SQL generated for the largest realistic selection.
- Reproduce the request through the application after changing the query, not only in a database client.
If your driver supports binding Oracle collection types, that can also provide a table-like input. It requires a compatible SQL type and driver configuration; a comma-separated string bound to one scalar placeholder is not equivalent.
Report and Selection Filters
A report’s “select all” option can generate an enormous list of individual values. Where the report’s intended meaning is “all authorized records,” it may be better to express that business condition directly rather than enumerate every ID. Do not remove a filter that enforces authorization or tenant boundaries.
When creating an artifact-ID filter in IBM Engineering Requirements Management DOORS Next, use no more than 1000 IDs, according to IBM Support’s product-specific guidance. Exceeding that filter limit can surface through this exception chain:
Error 400: Bad Request java.lang.RuntimeException: java.sql.SQLSyntaxErrorException: ORA-01795
For that product, reduce the artifact-ID selection or use another supported filter that identifies the intended artifacts without enumerating them. Rerun the filter and compare the returned artifact IDs with the intended selection.
If you divide a report into separate runs, do not assume their totals, sorting, or pagination can simply be combined.
Packaged Application Patches
If a packaged application generates the SQL internally, a supported application fix may be more appropriate than modifying database objects or vendor code.
For a historical Oracle Retail Invoice Matching Automatch failure, the Oracle Retail Invoice Matching release notes identify defect 7447861 in hot fix ReIM 12.0.9.1. Before applying that hot fix, ReIM 12.0 and all patches through ReIM 12.0.9 must already be installed.
That is a release-specific fix, not a general patch for modern Oracle applications. Confirm that it applies to the installed product version and obtain the supported patch procedure. Back up all data before running its installation scripts; Oracle warns that the scripts do not preserve data.
Validate the applicable patch in a test environment, rerun Automatch, and reconcile the affected processing results before production deployment.
How to Confirm the Fix and Prevent Recurrence
Long IN lists can still cause performance problems below the expression limit. Splitting one oversized list into many valid lists addresses the syntax restriction, but not necessarily the query’s parsing cost or execution efficiency.
Before closing the issue, check the following:
- Boundary behavior: Test inputs below, at, and above the target release’s explicit-list ceiling.
- Result correctness: Test empty selections, duplicate IDs, null handling, inclusion filters, and exclusions.
- Additional conditions: Confirm that status, date, authorization, and tenant filters still apply to every branch.
- Transaction behavior: For temporary tables, verify connection reuse, commit timing, rollback, and cleanup.
- Performance: Compare execution plans, elapsed time, and resource use with realistic data volumes.
- Application behavior: Rerun the original report, endpoint, job, or UI action that failed.
Prevent recurrence at the input boundary. Decide explicitly what an empty selection means, bind values rather than concatenating them, and avoid converting database query results into huge SQL strings.
For a recurring workload, the next step is to add a regression test containing a selection larger than the server’s explicit-list ceiling and verify that the application still returns the correct result.
Frequently Asked Questions
Does Oracle Limit the Total Number of Bind Parameters?
ORA-01795 is not a statement-wide bind-parameter limit; it concerns expressions in an individual list. Each scalar bind placeholder inside an explicit list still occupies an entry, so replacing literals with placeholders alone does not fix it. Other database or driver limits may apply independently.
Can I Increase the Limit in PL/SQL?
There is no supported PL/SQL setting or initialization parameter that raises this SQL expression-list ceiling on an affected release. Executing the statement through PL/SQL or dynamic SQL does not remove the restriction. Change the query structure or evaluate a database release whose documented limit meets the requirement.
Why Does ORA-01795 Appear in SQL Developer?
The Oracle database server returns the error; SQL Developer displays it. Increasing the client’s memory, changing its display settings, or reinstalling it will not change the server’s expression-list limit. Execute a corrected query against the same connection and verify the result.
Does ORA-01795 Prove That an Oracle WACS Pulldown Has an Item Limit?
No. ORA-01795 identifies a database expression-list restriction, not a universal item cap for Oracle WACS menus. Have the application administrator capture the failing request and generated SQL, then check the installed WACS release before treating the behavior as a documented UI limit.
Can I Split the Work Into Separate Queries Instead?
Yes, but separate queries are not automatically equivalent to one query with several correctly grouped predicates. Global sorting, pagination, aggregates, and duplicate handling must account for the complete result set; separate statements can also observe data at different times. Prefer a single rewritten statement when preserving those semantics matters.
