Member Avatar for Tank50

HI Guys

These days I am checking whether amount is correctly upload into one of our table its located in oracle database below query used to check this
.
Select * from Amount_Paiad where CusID IN ('34343','34343','090094')

There is no issue in query.Problem is how many values I can enter to IN Statement.There is more than 5000records.

Problem is once I enter 5000 records into sql statement ,sql developer and sql navigator generate error message saying ' Only 1000 records can insert to IN statement.

I only have read access for database tables.Please let me is there more better way to do this,is there way to increase the values(More than 1000) used IN Statement.

Thanks
Tank50

  • 7 Contributors
  • 6 Replies
  • 534 Views
  • 1 Month Discussion Span
  • Latest Post Latest Post by Prateek nandan

Dani AI

Generated

Oracle imposes a 1000-expression limit for a single IN list (ORA-01795), which is why the query fails once you pass that threshold — the client shows the error even though the SQL is syntactically fine for smaller lists. ran straight into that limit; several replies here already point to using a table or subquery, which is the usual long-term fix.

A quick, no-privileges workaround is to split the literal list into chunks of at most 1000 values and combine them with OR. This can be generated automatically by whatever creates your list, so no DB changes are needed:

SELECT *
FROM Amount_Paiad
WHERE CusID IN ('id1','id2',...,'id1000')
   OR CusID IN ('id1001','id1002',...,'id2000')
   -- repeat for remaining chunks

A cleaner and usually faster approach that requires no object creation on the server is to convert the literal list into an Oracle nested table and join it with TABLE(...). Built-in collection types exist, so a read-only user can often use them directly (use the string list type for text IDs or the numeric list for numbers):

SELECT a.*
FROM Amount_Paiad a
JOIN TABLE(sys.odcivarchar2list('34343','34344','090094',...)) t
  ON a.CusID = t.COLUMN_VALUE;

If the values originate from a file or application, loading them into a real table (or letting the app produce a subquery) is best for performance and lets the optimizer use indexes — that is the direction suggested by , and . For immediate checks, chunking or the TABLE(sys.*list) method will solve the 1000-item limit; after that, run EXPLAIN PLAN and compare execution times to choose the production approach.

Recommended Answers

All 6 Replies

Member Avatar for urtrivedi

how u form the list of 5000 items, is it somewhere stored in same database. or you do it in your front end.

Member Avatar for debasisdas

You need to pass the list of values as a subquery.

Member Avatar for Member #647493 
Select * from Amount_Paiad where CusID IN
(Select CusID From Customers_Tbl
 Where Cust_Stat = 12);

...or something similar.

Member Avatar for yash_792

if you are able to create a temporary table of your 5000 records then you can either join these tables or you can pass these values into a sub-query for e.g.

select * from amount_paiad where cust_id in (select temp_field_name from temp_table)

Hope this is helpfull

Member Avatar for Prateek nandan

HI Guys

These days I am checking whether amount is correctly upload into one of our table its located in oracle database below query used to check this
.
Select * from Amount_Paiad where CusID IN ('34343','34343','090094')

There is no issue in query.Problem is how many values I can enter to IN Statement.There is more than 5000records.

Problem is once I enter 5000 records into sql statement ,sql developer and sql navigator generate error message saying ' Only 1000 records can insert to IN statement.

I only have read access for database tables.Please let me is there more better way to do this,is there way to increase the values(More than 1000) used IN Statement.

Thanks
Tank50

if the record have id in serial no so you can use between(value1,value2)..

Edited by mike_2000_17 because: Fixed formatting
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.