Paginated report - multivalue parameter- pass as an array to Oracle dataset query

[10246532]

Hi all

I need to create a paginated report with 

- a multivalue parameter PeopleIDs, specifying a list of person IDs

- a dataset connected to an Oracle database, using that parameter

I used the suggested path, for the dataset

- defining the parameter expression as :peoplepar =JOIN(Parameters!PeopleIDs.Value,”,”)

- joining the source table with a table function that splits that expression and returns a collection

     SELECT some_cols FROM source_table st JOIN TABLE(split_tf(:people_par)) par ON (st.id = par.column_value)

The report is passing a single string, concatenating parameter values, and the data source undoes this, splitting the string into a collection, before using it.

It works fine till one or two thousand of values; increasing the number of values this approach lead to errors;

isn't there a way to pass directly a collection to Oracle?

thanks

Luigi

[10246532]

Hi v-bmanikante
thanks for your replay

I opted for the comma-separated string approach, adding to the report a second parameter in order to paginate the multivalue parameter.

It is not clear the second approach (GTT); how can i load the IDs into a temporary table first?

bye

Luigi

[v-bmanikante]

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:

1. Create a custom type in Oracle (like number_table which is a table of NUMBERs).
2. 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)).
3. 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 - Microsof...

Pass a report parameter within a URL - SQL Server Reporting Services (SSRS) | Microsoft Learn

Tutorial: Add parameters to your report (Report Builder) - SQL Server Reporting Services (SSRS) | Mi...

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

[10246532]

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

[v-bmanikante · Accepted solution]

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.

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

[10246532]

Thanks Akash_Varuna,

that is the question: how can I pass an array from a paginated report to Oracle?

[Akash_Varuna]

Hi @10246532  Instead of passing a concatenated string, you can use a PL/SQL associative array or a pipelined table function to pass the parameter as a collection. You could aslo try to load the values into a global temporary table (GTT) and use it in your query.