Multiple Parameter List - With 2 Column Values Concatenated (BI Publisher)

Summary

ORA-00909: invalid number of arguments message when executing report with multiple parms.

Content

It is a business requirement to have Cost Centre code and Description in the drop down list.  Ie.

nnnn-<description>

Example 1:

1105-Chief Executives Office

1108-Board & Chair

When executing the BI publisher report and selecting multiple values the query (snippet) as follows has a ORA-00909 error. (codes.segment2 is the cost centre code eg. 1105   and the parameter :p_costcentre contains multiple values selected (see Example 1 above).

..

AND (codes.segment2 IN (SELECT substr(token,1,instr(token,'-')-1)

         from  (select regexp_substr(:p_costcentre, '[^,]+', 1, LEVEL) token