CODEWITHSUNDEEP
sqloracle-databasereport
Roostam
Roostam

Reputation: 55

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

I need to add a parameter that has about 17000 values. And they constantly change. I want "all" to be the default value, but in that case I am unable to pass all 17,000 values in for the parameter due to IN lists being capped at 1,000 entries by the database.

When I try to pass all 17,000 values, I get an "ORA-01795: maximum number of expressions in a list is 1000" error.

I understand why Oracle doesn't allow more than that. Is there a way to overcome this problem?

Upvotes: 2

Views: 13341

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Do not pass in all 17,000 values when you want all of them. Do this instead.

select * 
from abc 
where (segment = :segment OR :segment IS NULL)

When the user does not select a segment parameter value, this will return data for all the segments.

If you want your front end to present "all" as a value, you could do this as a simple variation:

select * 
from abc 
where (segment = :segment OR nullif(:segment,'all') IS NULL)

Upvotes: 2

Related Questions