6.5.2 Filtering on a Page Item's Value

You can use any page item's value in a query filter in another region. Most page item types, including single-selection list-driven items, store just one value that can also be null.

Assuming your query by example page item is a Select List named P5_SELECTED_DEPARTMENT, you can reference its value in the WHERE clause of another region on the page using the following syntax:

DEPTNO = :P5_SELECTED_DEPARTMENT

If the page item's value can be null, then your WHERE clause must anticipate this accordingly using this modified version of the filter clause:

/*  Either no department is selected, 
 or match the selected department */
(   :P5_SELECTED_DEPARTMENT IS NULL 
 OR DEPTNO = :P5_SELECTED_DEPARTMENT)

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.