2

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...

garuda
  • 67
  • 1
  • 2
  • 8
  • 1
    I would consider putting the values in an external table, or passing them as an array rather than trying to force them into an `in` clause - [here's an example using numbers](http://stackoverflow.com/a/18675238/266304), which can fairly easily be adapted for strings. – Alex Poole Feb 10 '14 at 10:43

1 Answers1

4

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
)
Devolus
  • 21,661
  • 13
  • 66
  • 113