?
Solved

ORA-01795: maximum number of expressions in a list is 1000

Posted on 2007-11-20
4
Medium Priority
?
8,065 Views
Last Modified: 2008-02-01
i have an application that is running against an oracle10g database.  when i try to perform certain operations in the application, it comes back with the error:

ORA-01795: maximum number of expressions in a list is 1000

then, i look at the sql that is generated and i have:

select <some things> from <table> where <a thing> IN (thing1, thing2, 1000+ more);



can i accomidate more than 1000 expressions in IN clause?if not accomdate in oracle how to write it in java(if there are 5000 records)?
0
Comment
Question by:chaitu chaitu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20319344
>can i accomidate more than 1000 expressions
only if the expression is a query.


0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 2000 total points
ID: 20319348
As said you cannot, So what you can do as a work around is put all those values in a temporary table and then write IN (Select Value From TempTable) kindoff.
0
 
LVL 9

Expert Comment

by:ysnky
ID: 20319360
you can split if more than 1000 like this;
select val from table where (col in (1...1000) or col in (1001...2000) )

but it is not efficient but works.
0
 
LVL 9

Expert Comment

by:ysnky
ID: 20319375
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question