how to select a list of 10,000 unique ids from dual in oracle SQL

Question

So I can't create or edit tables (I'm a user with read only permission) and I want to look up 10,000 unique id's. I can't put them inside of an IN() statement because oracle limits over 1000 items.

Is it possible to select this entire list from the DUAL table in oracle? Something like:

select  
'id123,id8923,id32983,id032098,id308230,id32983289'  
from DUAL

Answer 1

Use a collection (they are not limited to 1000 items like an IN clause is):

SELECT COLUMN_VALUE AS id
FROM   TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       )

SYS.ODCIVARCHAR2LIST and SYS.ODCINUMBERLIST are collection types that are supplied in the SYS schema.

You can join this directly to whichever table you are SELECTing from without needing to use the DUAL table:

SELECT y.*
FROM   your_table y
       INNER JOIN TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
       ON (y.id = i.COLUMN_VALUE);

If you can get a collection type created then you do not even need the TABLE expression and can use it directly in the WHERE clause using the MEMBER OF operator:

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(200);
/

SELECT *
FROM   yourtable
WHERE  id MEMBER OF stringlist(
                      'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
                    );

You can even pass the values as a bind parameter - see my answer here

Answer 2

Oracle still doesn't support the VALUES row constructor, so there are only two ugly workarounds:

The 1000 item limit does not apply for multi-column IN conditions

Expression Lists

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.

so you can do:

where (1,id) in ( (1,'id123'),
                  (1,'id8923'),
                  (1,'id32983'), 
                  (1,'id032098'), .... )

Or using a big ugly UNION ALL:

with idlist (xid) as (
  select 'id123' from dual union all 
  select 'id8923' from dual union all 
  .....
  select 'id32983' from dual 
)
select ...
from some_table
where id in (select xid from idlist);

Answer 3

One solution is the WITH clause:

with ids as (
   select 'id123' as uid from dual union all
   select 'id8923' as uid from dual union all
   select 'id32983' as uid from dual union all
   select 'id032098' as uid from dual union all
   select 'id308230' as uid from dual union all
   select 'id32983289' as uid from dual 
)
select *
from ids
     join your_table yt
     on yt.id = ids.uid  

This may seem like a bit of a chore but presumably you have your list of UIDs in a spreadsheet or whatever. If so it's a cinch to generate those select statements using regular expressions. Just cut'n'paste the column into an editor which supports regex search and replace.

Answer 4

Yet another work-around

select  *

from    t

where   id in ('id1','id2','id3',...,'id1000')
     or id in ('id1001','id1002','id1003',...,'id2000')
     or id in ('id2001','id2002','id2003',...,'id3000')
     or ...