Oracle's input parameters limit

Recently I stomped on an odd problem. One of our SQL selects failed with an error "ORA-01795: maximum number of expressions in a list is 1000". The problem was, that our IN expression had more than 1500 elements.

First it seamed like an unsolvable problem. I was really worried that I will have to split our call into multiple calls with less parameters and then combine and filter the results. That would be really painful. But there is a simple workaround.

Instead of this select (which will throw an error):

SELECT * FROM foo WHERE some_param IN ('1', ..., '1500')

You can rewrite your SQL select:

SELECT * FROM foo WHERE some_param IN ('1', ..., '999') OR

some_param IN ('1000', ..., '1500');