Oracle Error : Maximum number of expressions in a list is 1000

Question

I am working in C#.Net and Oracle. i am passing a string to a query. i had used this code for concating all the item id's

List<string> listRetID = new List<string>();
                foreach (DataRow row in dtNew.Rows)
                {
                    listRetID.Add(row[3].ToString());
                }

This concatination goes above 10,000. so i am getting the error message like this..

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

How to fix this..

No i cant..Its a very big query. As i said, the input for that query contains a string [ The concat data of that string crosses 10000 id's Seperated by commas] — RobinHood, Jul 19 '12 at 12:07
then restructure your query , as I do not think this is a good idea to add 10000 id(s) to form a string is a good idea — Satya, Jul 19 '12 at 12:16

score 4 · Answer 1 · answered Jul 19 '12 at 12:15

The documentation states:

A comma-delimited list of expressions can contain no more than 1000 expressions. A comma-delimited list of sets of expressions can contain any number of sets, but each set can contain no more than 1000 expressions.

Presumably you're using this string as the contents of in IN (...) restriction, in which case there isn't really anything you can do - this just won't work. A common way to work around this is to generate a dummy table as a subquery or common table expression (CTE) and joining to that, but I'm not sure how you'd translate your List - possibly similar to whatever you're doing with your IN clause. You'd want to end up with your query looking something like:

with tmp_tab as (
    select <val1 from list> as val from dual
    union all select <val2 from list from dual
    union all select <val3 from list from dual
    ...
)
select <something>
from <your table> yt
join tmp_tab tt on yt.<field> = tt.val

But that requires generating the entire (huge) query including the CTE each time you run it, and there's no opportunity to use bind variables.

You might find something like this approach more palatable.

this is pretty clear. You have to do that in multiple queries with ID batches of max 1000 id's and than stick the results together one way or another. — Hugo, Jul 19 '12 at 12:53

score 1 · Answer 2 · answered Jul 19 '12 at 12:52

1

You can have 10 lists of 1000 items instead of 1 list of 10000 items.

WHERE some_column IN (1,2,...,1000)
   OR some_column IN (1001,1002,...2000) -- etc.

answered Jul 19 '12 at 12:52

jva

2,797
1
26
41

how can i take that 1000 id's out from the list of 10000 id's..the values are not hardcoded. all are dynamic. – RobinHood Jul 19 '12 at 13:06
Sorry, I can't give you C# code :( Probably you can use a list of List and switch to new element after 1000 values. – jva Jul 19 '12 at 13:09

score 0 · Accepted Answer · edited May 23 '17 at 10:09

0

Not a C# guy but I would just split the list listRetID in multiple lists or create a list of lists

Then loop through that list of lists and perform the query on each element of the list.

edited May 23 '17 at 10:09

Community

1
1

answered Jul 19 '12 at 13:08

Hugo

12,334
6
30
36

Olaf · Answer 4 · 2012-07-19T14:54:24.057

What is the intent of your query?

It looks like you are selecting rows that have some column equal to the 3rd column of one of the records of some query.

The correct way of doing this is either an SQL join or a subquery. There is absolutely no need to bring this into C# code. For example, using a subquery you can write something like this:

SELECT *
FROM atable
WHERE afield IN (
    SELECT field3
    FROM someothertable)

Oracle Error : Maximum number of expressions in a list is 1000

4 Answers4