Multiple selection for Parameter values in XML Publisher

With working BIP Enterprise 10.1.3.2, we can't use all three options (Single, Multiple and All) with using of Oracle provided options in BIP. You can use only two options of them at runtime.

Symptoms:

****CASE 1 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (:myParameter)

• All does not work
• Single Selection is working
• Multiple Selection is working

***********CASE 2 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (nvl(:myParameter, DEPARTMENT_NAME))

• All is working now
• Singe selection is working
• Multiple selection is not working any more (ORA-00909: Ungültige Anzahl von Argumenten / illegal amount of arguments)..

***********CASE 3 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS where DEPARTMENT_NAME IN (:myParameter) or :myParameter IS NULL

• All is working
• Singe selection is working
• Multiple selection is not working (ORA-00920: Ungültiger relationaler Operator / illegal relational operator)

Solution:

select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS
where ((coalesce(null, :Parameter) is null) or (DEPARTMENT_NAME in (:Parameter)))