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