Solved

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

Posted on 2007-11-20
4
8,036 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
  • 2
4 Comments
 
LVL 142

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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
hibernate insert example 13 29
Detect Closed Loops (circles, figure-8s, etc) in PNG Images 6 33
having some issue on pl sql procedure 1 19
Oracle collections 15 23
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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