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,
       TABLE(
         SYS.ODCIVARCHAR2LIST(
           'id123', 'id8923', 'id32983', 'id032098', 'id308230', 'id32983289'
         )
       ) i
WHERE  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