We use Oracle 11gR2 on win2008R2.
We wrote query like below in where phrase.
(COALESCE(:p_site, NULL) IS NULL)
OR (
COALESCE(:p_site, NULL) IS NOT NULL
AND EXISTS (
SELECT
*
FROM
MART.REPORTERS
INNER JOIN MART.REPORTER_TYPE
ON REPORTER_TYPE.RPTR_TYPE_ID = REPORTERS.REPORTER_TYPE
WHERE
REPORTERS.CASE_ID = RPT_CASE.CASE_ID
AND REPORTER_TYPE.E2B_CODE IN (1, 2, 3)
AND REPORTERS.INSTITUTION_J IN (:p_site)
)
)