Getting data using Procedure by using the SYS_REFCURSOR.

Hi Sobha,

You areusing a multi-value parameter ":prSubscriber" in Power BI, which works fine when a single value is selected. However, when multiple values are selected, you're encountering these errors.

This happens because Power BI passes each selected value as a separate parameter. So, if you select three values, Power BI tries to pass them as three separate parameters, but your stored procedure expects a single parameter. This mismatch leads to the errors you're seeing.

To handle multiple values, you can modify your stored procedure to accept a comma-separated string and then parse it into individual values within the procedure. You can do it by :
* First create a function to split the Comma-Separated string.

CREATE OR REPLACE FUNCTION split_string(p_list IN VARCHAR2)
RETURN SYS.ODCIVARCHAR2LIST
AS
l_list SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
l_index PLS_INTEGER := 1;
l_pos PLS_INTEGER := 0;
l_str VARCHAR2(4000) := p_list;
l_item VARCHAR2(4000);
BEGIN
LOOP
l_pos := INSTR(l_str, ',', l_index);
EXIT WHEN l_pos = 0;
l_item := TRIM(SUBSTR(l_str, l_index, l_pos - l_index));
l_list.EXTEND;
l_list(l_list.COUNT) := l_item;
l_index := l_pos + 1;
END LOOP;
l_item := TRIM(SUBSTR(l_str, l_index));
IF l_item IS NOT NULL THEN
l_list.EXTEND;
l_list(l_list.COUNT) := l_item;
END IF;
RETURN l_list;
END;

* Now modify your stored procedure to use the split function.

CREATE OR REPLACE PROCEDURE DORIS.XYZ_collections_select_test(
prBeginDate IN VARCHAR2 := '2024',
pGroupID IN VARCHAR2 := NULL,
prSubscriber IN VARCHAR2,
c_direct_reports OUT SYS_REFCURSOR
)
AS
l_subscribers SYS.ODCIVARCHAR2LIST;
BEGIN
l_subscribers := split_string(prSubscriber);

OPEN c_direct_reports FOR
SELECT *
FROM your_table
WHERE subscriber_id IN (SELECT COLUMN_VALUE FROM TABLE(l_subscribers));
END;

* Now adjust your PowerBi report. You will need to concatenate the selected values into a single comma-separated string before passing them to the stored procedure. You can do it in your Power BI report, by creating a new parameter (e.g., prSubscriberString) that concatenates the selected values "Text.Combine(prSubscriber, ",")". Use this prSubscriberString parameter when calling the stored procedure.

By modifying your stored procedure to accept a comma-separated string and adjusting your Power BI report to pass the selected values as such, you should be able to handle multi-value parameters without encountering the binding errors.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team