I'm running jOOQ integration tests for large conditions of the form
WHERE x IN (:1, :2, :3, :4, ..., :3001, :3002)
The above example depicts that there are many bind variables in an IN condition. Oracle has a well-known limitation of 1000 values (bind values or inline values) between the parentheses of an IN condition. The solution is simple, just write:
WHERE x IN (:1, :2, :3, :4, ..., :1000) OR x IN (:1001, ...)
Sybase ASE 15.5 and SQL Server 2008 R8 on the other hand seem to have an overall limitation on the number of bind values: 2000 for Sybase ASE and 2100 for SQL Server respectively. In other words, there seems to be no way to split / transform the above condition using bind values for these two databases. Is there any way to circumvent that problem, short of inlining all bind values?
The way we've finally implemented this in jOOQ is by using a ControlFlowException to abort rendering of SQL with bind values once we've encountered any limit. The limits are:
We've also blogged about this here:
Once this limit is hit, the ControlFlowException is caught at the query rendering site, where it is simply re-rendered with all bind values inlined - which always works (until you hit the query size limit, if any, of course).
We're assuming that inlined bind values, and the repetitive hard-parses that incur are OK in these cases, as chances that an execution plan can really be reused with such highly dynamic SQL are slim.