ORA-01795: maximum number of expressions in a list is 1000 , how to split the string

Question

How to split the following string?

      A.x IN (changeList),

changeList has the following value, so that it makes the clause like this:

       A.x IN (HEXTORAW('1E2956B9266F11DDA85810000000C959'),HEXTORAW   
             ('ADD834AD6A3911DF923C10000000C959'),HEX...........

The above IN has more than 1000 values and hence ORA-01795 error, how to modify it so that i have

A.X IN (id1, ..id999) OR A.x IN (id1000,...), Any c code would help me...

Answer 1

You must write a loop that executes the SQL in chunks with 1000 entries each.

Alternatively you can insert the values into a table and then do a subselect with the IN clause, because with a subselect, the limitation of 1000 entries doesn't apply. This limit is only when you use an SQL with an hardcoded value string.

Something like this:

select *
from mytable t
where t.column1 = value
and t.column2 in
(
    select my_values
    from my_temp_table
)