Objective is to fix the problem when selecting multiple values from a parameter then SQL throws exception: ORA-00909: invalid number of arguments
Create data set and pass the parameter as shown
In general BI Publisher supports all, single, multiple values to be passed as filters to the Report. But to handle show multiple (all) values including null this functionality
can be achieved using the condition as shown.
select STATE_CODE,
STATE_NAME
from
WC_STATE_D
where
COUNTRY='US'
and (STATE_CODE IN (:states) or 'All' IN (:states ||
'All'))
order by
STATE_NAME ASC
Create a parameter and select list of values defined for this parameter
A menu type parameter "states" presents state LOVs to the user from which to select a value or values to pass to the data source. Define the list of values as "States List" using SQL query type.
 |
| Parameter - menu type |
Note: Choose any parameter option NULL Value Passed or All Values Passed.To allow multiple entries from the list, selected "Multiple Selection" option.
If the parameter values are not dependent on any other parameters, uncheck 'Refresh other parameters on change" option.
Click View Data and select All from the list menu.
The following figure shows how the states menu type parameter will
display list of state(s) in the report when multiple selection is enabled:
 |
| All states |
 |
| Selected state |
I hope the above solution is helpful to avoid the sql error ORA-00909: invalid number of arguments
