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.
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