oracle - How to resolve ORA-01795 in Java code

Question

I am getting ORA-01795 error in my Java code while executing more than 1000 records in IN clause. I am thinking to break it in the batch of 1000 entries using multiple IN clause separated by OR clause like below:

  select * from table_name
  where
  column_name in (V1,V2,V3,...V1000)
  or
  column_name in (V1001,V1002,V1003,...V2000)

I have a string id's like -18435,16690,1719,1082,1026,100759... which gets generated dynamically based on user selection. How to write a logic for condition like 1-1000 records ,1001 to 2000 records etc in Java. Can anyone help me here?

Answer 1

There are three potential ways around this limit:

1) As you have already mentioned: split up the statement in batches of 1000

2) Create a derived table using the values and then join them:

with id_list (id) as (
  select 'V1' from dual union all
  select 'V2' from dual union all
  select 'V3' from dual
)
select *
from the_table
where column_name in (select id from id_list);

alternatively you could also join those values - might even be faster:

with id_list (id) as (
  select 'V1' from dual union all
  select 'V2' from dual union all
  select 'V3' from dual
)
select t.*
from the_table t
  join id_list l on t.column_name = l.id;

This still generates a really, really huge statement, but doesn't have the limit of 1000 ids. I'm not sure how fast Oracle will parse this though.

3) Insert the values into a (global) temporary table and then use an IN clause (or a JOIN). This is probably going to be the fastest solution.