Comma separated values in SQL Query Data Set in Oracle BI

This tutorial will show how to pass comma-separated parameter values in SQL Query Data Set in Oracle Business Intelligence Publisher.

Use case: Let’s say we have an integration that invokes a BIP report. The integration will pass comma/pipe-separated values to the BIP report and expects a response from the BIP query.

The following query is written to handle the comma-separated values that are passed as a parameter:

select TRX_NUMBER, TRX_DATE, CREATION_DATE
from RA_CUSTOMER_TRX_ALL
where TRX_NUMBER in (
select regexp_substr(upper(:p_TRX_NUMBER),'[^,]+', 1, level) from dual
connect by regexp_substr(upper(:p_TRX_NUMBER), '[^,]+', 1, level) is not null )

When we run this query in Oracle Oracle Business Intelligence Publisher and pass 1001,1002,1003,1044,1045 as the parameter values we see the output as follows:

That’s all about this post. Thank you for reading. If you like the article please like, comment, and share.

Also, please subscribe to our YouTube channel to learn more about Oracle stacks.

Further Readings

Parameterized SQL Query Data Set in Oracle BI

How to create SQL based Data Set in Oracle Business Intelligence

Parameterized SQL Query Data Set in Oracle Business Intelligence