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

Question

5.00/5 (1 vote)

See more:

Hi, I 'm trying to select 170k records from a oracle database, there are some how to avoid this error? or any way to improve this query ?

thanks.

select sr.RELATED_PON, srsi.VALID_VALUE 
from SERV_REQ sr
inner join SERV_REQ_SI_VALUE srsi
on sr.DOCUMENT_NUMBER = srsi.DOCUMENT_NUMBER
inner join SERV_ITEM si
on si.SERV_ITEM_ID = srsi.SERV_ITEM_ID
and si.STATUS = '6'
where srsi.VALUE_LABEL = 'unitAddress'
and srsi.VALID_VALUE in ('1682511819',
'1682575135',
'1682580326'
... more than 150k here!
)

Posted 25-Mar-15 10:40am

Laxmax1

Add a Solution

Comments

ZurdoDev 25-Mar-15 16:54pm

You have more than 150 thousand values in the IN clause? What?!!!!

Laxmax1 25-Mar-15 16:58pm

if you have a better idea, help me to improve. :)

ZurdoDev 26-Mar-15 7:32am

There has to be a better idea. Why are you doing what you are doing?

jgakenhe 25-Mar-15 16:58pm

Sounds like you're using Oracle 8. Try to convert inner joins to subqueries and you'll probably have success.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sascha Lefèvre · Accepted Answer · 2015-03-25T11:26:00

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

You're slightly above that ;)

You have some options:

1) Very quick & dirty and only recommended if this is a one-time query:
Split your IN-clause into multiple OR'ed IN-clauses where each one only contains max. 1000 values.

2) Create a temporary table, insert the values of your current IN-clause there and change your current query so that it does an inner join on that temporary table instead of the IN-clause.

3) Use something what in SQL-Server would be called Table Valued Parameters, I think it's a User Defined Type in Oracle, take a look here:
http://stackoverflow.com/questions/10217922/how-to-use-array-table-parameter-to-oracle-odp-net-10g-via-ado-net-c[^]

Edit: Actually 3) isn't a whole new option, it's just an alternative way to 2) of inserting the values into a temporary table.