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 :

1. 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).
2. 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.
3. 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.

Regards,

B Manikanteswara Reddy