Include more than 1000 Values in Oracle IN Clause

In Oracle we can’t include more than 1000 values in the “IN” clause.

To get around this limitation, you divide the string of comma delimited values to be included into two or more parts using SUBSTR or through any similar other function and then these new strings should be placed in the IN clause of different SQL statements that are later combined using the UNION operator.

Sample Code

First create two hidden parameter that include the initial first and second half of the selected values of the parameter. For this write a custom code can be used.

Sample code to break the parameter into two hidden parameter of half the original length.

Public Function Group1(ByVal parameter as Parameter) as String
 Dim s as String
 s=String.Empty
 If parameter.IsMultiValue then
 For i as integer = 0 to parameter.Count/2
 s = s "" CStr(parameter.Value(i)) + ","
 Next

Else
 s = CStr(parameter.Value(0))

End If
 Return s
 End Function

Public Function Group2(ByVal parameter as Parameter) as String
 Dim s as String
 s=String.Empty
 If parameter.IsMultiValue then
 For i as integer = (parameter.Count/2) to parameter.Count-1
 s = s "" CStr(parameter.Value(i)) + ","
 Next

Else
 s = ""
 End If
 Return s
 End Function

Sample Query

New we use two SQL queries including the two hidden parameters joined by the UNION operator

SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup1)
UNION
SELECT * FROM TABLE_1 WHERE VALUE_1 IN (:prmHiddenInspectorFirstGroup2)

Obviously, this example breaks when you get to 2000 values, but it is presented here as a sample approach that can be easily modified to suit more advanced situations.

The post Include more than 1000 Values in Oracle IN Clause appeared first on OptimusBI.