In Clause limit in oracle

Question

i am dynamically forming a query in oracle to select data where i give id's as input's. my query goes is as follows

select * from temp where temp_id in ( :Id);

my input id are in thousands so i am dynamically creating the in clause in such a way that my final query is as follows

select * 
from temp
where temp_id in (a1,a2,a3,....a999) 
  or temp_id in (b1,b2,b3,....b999) or so on ....

my problem here is even after that i am getting too many values exception. Can you please help me.

Answer 1

One approach is to use global temporary table:

CREATE GLOBAL TEMPORARY TABLE my_temp_table (
  id           NUMBER
)
ON COMMIT DELETE ROWS;

-- multiple INSERT INTO

select * from temp where temp_id in (SELECT id FROM my_temp_table);
COMMIT;

Answer 2

to do it with a single request

select * from temp where id in (
    select a.letter||b.num from (
        select chr(rownum + 96) letter from dual connect by rownum < 3
    ) a 
    join (
        select rownum as num from dual connect by rownum < 1000
    ) b on 1=1
);

and you replace the value after "rownum <" with the number of letters you want + 1 (same thing for numbers)