ORA-00907: missing right parenthesis when create varray

Question

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?

Answer 1

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

Answer 2

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).