Filtering on a Multi-Selection Page Item

When a query-by-example item allows multiple selection, filtering by its value requires an IN clause with a subquery.

By default, the value of a multi-select page item will contain the colon-delimited (:) return values of the user’s selections. Assuming your item is named P5_SELECTED_DEPARTMENTS, you can reference its value in the WHERE clause of another region on the page using the syntax below. If you use a different delimiter, pass it instead of the colon in APEX_STRING.SPLIT()’s second parameter.

DEPTNO IN (SELECT COLUMN_VALUE
             FROM APEX_STRING.SPLIT(:P5_SELECTED_DEPARTMENTS,':'))

If the page item is optional, then your WHERE clause must anticipate that with this modified version of the filter clause:

/* Either no department is selected,
or match the selected departments */
(   :P5_SELECTED_DEPARTMENTS IS NULL
 OR DEPTNO IN (SELECT COLUMN_VALUE
                 FROM APEX_STRING.SPLIT(:P5_SELECTED_DEPARTMENTS,':')))

Caution: When a region data source references a page item as a bind variable, list that item in the region’s Page Items to Submit property. Omit the colon and comma-separate multiple item names. If you forget this step, the bind variable evaluates to null on refresh and the query may return no rows.