Forum Discussion
Paginated report - multivalue parameter- pass as an array to Oracle dataset query
Hi 10246532 ,
Thanks for the additional clarification.
You're correct , in Oracle (unlike SQL Server) you cannot directly bind a SSRS multi-value parameter (which comes as an array) into an Oracle table function without additional handling, especially when using standard OLEDB/ODAC drivers.
Issues might be:
- SSRS parameters are passed as a comma-delimited string when connecting to Oracle.
- Oracle table functions expect PL/SQL collection types, but OLEDB and ODAC 19.3 drivers don't natively support binding .NET arrays or multi-values to Oracle collection types directly.
- Hence, the error ORA-06553: PLS-306 ("wrong number or types of arguments"), because Oracle expects a strongly typed PL/SQL collection, not a simple string or implicit array.
You can try below workarounds :
- Keep using the comma-separated string approach
(your original split_tf table function is correct) , but optimize your split_tf function for larger input (for example, avoid recursive splitting). - You can also try,
- Use Oracle Global Temporary Tables (GTTs):
- Load the IDs into a temporary table first (via a preprocessing step).
- Then your query simply joins the GTT.
- Downside: This requires more control over your reporting pipeline and perhaps a stored procedure.
- Even you can try,
- If your Oracle driver and environment allows, try ODP.NET managed driver, which supports binding collections better (still requires custom coding though).
Actually, There’s no native way to pass a true array from SSRS into Oracle via ODAC 19.3 without string-splitting or temp tables.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi 10246532 ,
Thank you for reaching out to Microsoft Fabric Community forum.
Akash_Varuna Thank you for your quick response.
If you're running into issues with passing a large number of values through a multi-value parameter in a paginated report (like PeopleIDs), especially when using Oracle as the data source, the string-splitting approach might not scale well.
A better and more efficient solution is to use Oracle collection types. Instead of passing a long comma-separated string and splitting it in SQL, you can:
- Create a custom type in Oracle (like number_table which is a table of NUMBERs).
- Write a stored procedure or function that accepts this collection type as input and filters your query using IN (SELECT column_value FROM TABLE(p_ids)).
- Call this procedure from your report, mapping the multi-value parameter directly to the collection.
This method is cleaner, faster, and doesn't break when you have thousands of IDs to pass.
You can check the following Microsoft documents for better understanding and for alternate solutions:
How to pass multi value parameters in SSRS for the Oracle Database using OLEDB Connection - Microsoft Q&A
Pass a report parameter within a URL - SQL Server Reporting Services (SSRS) | Microsoft Learn
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi v-bmanikante
thanks for your replay; I've already seen that documents.
It seems that a multivalues parameter can be passed to a Transact-SQL instance,
specifying a wher clause like that: "WHERE column IN (@parameter)".
Unfortunately, a similiar approach fails with a Oracle instance;
I tried to define a table function accepting a collection, and to invoke it with this query:
SELECT column_value val
FROM TABLE(my_tf(:Parameter))
I obtained the error
ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TF'
I defined the input parameter of table function alternatively as
- a nested table,
- an associative array,
- a varray;
none of these is correct.
I am using driver odac 19.3
bye
Luigi
Hi 10246532 ,
Thanks for the additional clarification.
You're correct , in Oracle (unlike SQL Server) you cannot directly bind a SSRS multi-value parameter (which comes as an array) into an Oracle table function without additional handling, especially when using standard OLEDB/ODAC drivers.
Issues might be:
- SSRS parameters are passed as a comma-delimited string when connecting to Oracle.
- Oracle table functions expect PL/SQL collection types, but OLEDB and ODAC 19.3 drivers don't natively support binding .NET arrays or multi-values to Oracle collection types directly.
- Hence, the error ORA-06553: PLS-306 ("wrong number or types of arguments"), because Oracle expects a strongly typed PL/SQL collection, not a simple string or implicit array.
You can try below workarounds :
- Keep using the comma-separated string approach
(your original split_tf table function is correct) , but optimize your split_tf function for larger input (for example, avoid recursive splitting). - You can also try,
- Use Oracle Global Temporary Tables (GTTs):
- Load the IDs into a temporary table first (via a preprocessing step).
- Then your query simply joins the GTT.
- Downside: This requires more control over your reporting pipeline and perhaps a stored procedure.
- Even you can try,
- If your Oracle driver and environment allows, try ODP.NET managed driver, which supports binding collections better (still requires custom coding though).
Actually, There’s no native way to pass a true array from SSRS into Oracle via ODAC 19.3 without string-splitting or temp tables.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy