Not sure that using so many values in a IN() is that good, actually -- especially for performances.
When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :
ep_codes IN (...1000 ep_codes...)
OR ep_codes IN (...200 ep_codes...)
Does it make the results less strange ?
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
) 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)
Actually you can use collections/multisets here. You'll need a number table type to store them.
CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;
WHERE period MEMBER OF NUMBER_TABLE(1,2,3...10000)
Read more about multisets here:
Seems like it would be a better idea, both for performance and maintainability, to put the codes in a separate table.
AND ep_code in (select code from ep_code_table)
could you insert the 1200 ep_code values into a temporary table and then INNER JOIN to that table to filter rows instead?
FROM mytable a
INNER JOIN tmp ON (tmp.ep_code = a.ep_code)