3

In my program there are a lot of situation when i need to get additional information about knowing ids. So i have list of ids, which length may be very long (for example 100000 elements in it).

How i can use this list and transfer in oracle for getting sql without using temp tables?

No i try to use collection:

CREATE TYPE TEST_VARRAY IS VARRAY(5000) OF NUMBER(18);

SELECT G.ID, G.NAME FROM ANY_TABLE G
WHERE G.ID IN
(
SELECT COLUMN_VALUE FROM TABLE(
NEW TEST_VARRAY
(0,1,2,3... and so on ...,995,996,997,998,999)
)
);

there are 1000 numbers. And when I try execute this query the error ORA-00907: missing right parenthesis tips is appeared! But if i delete first 0 (so we have 999 numbers) the sql is executed ok.

What is problem here?

Srini V
  • 11,045
  • 14
  • 66
  • 89
user1881712
  • 99
  • 1
  • 1
  • 5

2 Answers2

5

There is a limit in Oracle IN clause.

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.

Read here or here or here

Community
  • 1
  • 1
Srini V
  • 11,045
  • 14
  • 66
  • 89
  • Where are you see in this query "IN" clause? – user1881712 Sep 26 '13 at 10:37
  • SELECT G.ID, G.NAME FROM ANY_TABLE G WHERE G.ID IN ( – Srini V Sep 26 '13 at 10:39
  • You say about error "ORA-01795: maximum number of expressions in a list is 1000 tips". But in my case another error. – user1881712 Sep 26 '13 at 10:51
  • How can I to rewrite this query? The "0,1,2,3,4,5,6,7,8,9,10.." i put to query as parametr – user1881712 Sep 26 '13 at 10:52
  • 2
    @user1881712 The limit also applies for the number of parameters to an object constructor - in this case the VARRAY constructor. You can't create a varray with more than 1000 elements in the constructor. –  Sep 26 '13 at 10:58
  • Does exists way to create varray with more than 1000 elements? I have list with more 1000 elements in C# program. How i can use this list in a query? – user1881712 Sep 26 '13 at 11:04
  • Read the links I have posted. They have answers for your question @user1881712 – Srini V Sep 26 '13 at 12:06
  • I saw them. The most popular answer is partitioned list into like "IN (not more 1000 elements) OR IN (next not more 1000 elements) etc). But this way also is not working. You can see the problem from http://stackoverflow.com/questions/18945895/ora-00913-error-when-use-a-lot-of-in-statement Another solution - is using temp tables. But I want to use collection – user1881712 Sep 26 '13 at 12:26
  • Any in statement like x in (1,2,3) can be rewritten as (1,x) in ((1,1), (1,2), (1,3)) and the 1000 element limit will no longer apply. – Srini V Sep 26 '13 at 12:38
  • but what about collection? – user1881712 Sep 26 '13 at 12:49
  • I try this solution as (1,x) in ((1,1), (1,2), (1,3)) and the 1000 element limit. Yes it work on 10000 elements. But when i use 100 000 elements it does't work (see my another question http://stackoverflow.com/questions/18945895/ora-00913-error-when-use-a-lot-of-in-statement) – user1881712 Sep 26 '13 at 13:06
1

In my opinion, you are misusing collections, at least I am not sure something like you did is good.

As far as I understand you generate this query before run, so what is the problem to do like that?

with ids as (select /*+ materialize */ 1 id from dual union all
             select 2 from dual union all
             select 3 from dual union all
             select 4 from dual union all
             /* repeat with the ids you need */
             select 9999 from dual)
select *
from yourTable, ids
where yourTable.id = ids.id;

And that's it! Without any limitations, with pure SQL only. I have added materialize hint to ensure it is not performance relevant, but I think it can be skipped.

No temporary tables, no collections, nothing to create and support. Just SQL.

If you will put ids out of with into from clause it will work in any RDBMS (I guess).

smnbbrv
  • 23,502
  • 9
  • 78
  • 109