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

It doesn't say anywhere that these are contiguous numbers. In fact the sample shows totally random numbers — Nick.Mc, Dec 15 '16 at 03:19
You could also try `IN (SELECT C1 FROM (SELECT 'id123' C1 FROM DUAL UNION ALL SELECT 'id8923' FROM DUAL UNION ALL SELECT 'id32983' FROM DUAL))` — Nick.Mc, Dec 15 '16 at 03:21
Where are you getting those 10000 ids from? Isn't it a result of another query? Is it perhaps a list of all ids except for maybe a few? — Marcin Wroblewski, Dec 15 '16 at 09:53
Possible duplicate of [How to load a large number of strings to match with oracle database?](http://stackoverflow.com/q/34699223/1509264) — MT0, Dec 15 '16 at 10:02

MT0 · Accepted Answer · 2022-01-20T11:15:59.107

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

score 2 · Answer 2 · edited Dec 15 '16 at 08:47

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

edited Dec 15 '16 at 08:47

David דודו Markovitz

42,900
6
64
88

answered Dec 15 '16 at 07:16

Wow. How did you get to the multi-column work-around? Any documentation / official reference / semi-official reference? – David דודו Markovitz Dec 15 '16 at 08:16
@DuduMarkovitz: no I have no reference for that. – Dec 15 '16 at 08:20
Can you recall where/when did you see it for the first time? – David דודו Markovitz Dec 15 '16 at 08:23
@DuduMarkovitz: I saw this here on SO – Dec 15 '16 at 08:24
Did a quick search and add a reference to your answer. I hope it is O.K. by you. – David דודו Markovitz Dec 15 '16 at 08:48
wow what a strange thing, i guess i could write a function in excel to make this work, so great answer! in the end, i wrapped markovitz first answer inside a with() statement and got things running smooth :) – barker Dec 15 '16 at 18:17

score 0 · Answer 3 · answered Dec 15 '16 at 07:16

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.

score 0 · Answer 4 · answered Dec 15 '16 at 08:22

0

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

answered Dec 15 '16 at 08:22

David דודו Markovitz

42,900
6
64
88

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

4 Answers4

Linked

Related