Oracle SQL: How to use more than 1000 items inside an IN clause [duplicate]

Question

I have an SQL statement where I would like to get data of 1200 ep_codes by making use of IN clause. When I include more than 1000 ep_codes inside IN clause, Oracle says I'm not allowed to do that. To overcome this, I tried to change the SQL code as follows:

SELECT period, ...
FROM   my_view
WHERE  period = '200912'
       ...
       AND ep_codes IN (...1000 ep_codes...)
       OR  ep_codes IN (...200 ep_codes...)

The code was executed succesfully but the results are strange (calculation results are fetched for all periods, not just for 200912, which is not what I want). Is it appropriate to do that using OR between IN clauses or should I execute two separate codes as one with 1000 and the other with 200 ep_codes?

---

Pascal Martin's solution worked perfectly. Thanks all who contributed with valuable suggestions.

Answer 1

The recommended way to handle this in Oracle is to create a Temporary Table, write the values into this, and then join to this. Using dynamically created IN clauses means the query optimizer does a 'hard parse' of every query.

create global temporary table LOOKUP
(
    ID NUMBER
) on commit delete rows;

-- Do a batch insert from your application to populate this table
insert into lookup(id) values (?)

-- join to it
select foo from bar where code in (select id from lookup)