Getting data using Procedure by using the SYS_REFCURSOR.

[Sobha]

Have a Oracle procedure, which will insert data to the global temparary table and  selecting the data from the table using SYS_REFCURSOR as the output parameter.

 My procedure is as below 

CREATE OR REPLACE PROCEDURE DORIS.XYZ(
prBeginDate IN Varchar2 := '2024',
pGroupID IN Varchar2:= NULL ,
c_direct_reports OUT SYS_REFCURSOR)
AS

BEGIN

Insert into Sub_ids_list_xyz values ('10002521' );

OPEN c_direct_reports FOR
select * from Sub_ids_list_xyz;

END;
/

Calling the procedure as below in the Text in the Query Type 

Declare xx SYS_REFCURSOR;
Begin
DORIS.XYZ(
'2024',
NULL ,
xx);
DBMS_SQL.RETURN_RESULT(xx);
End;

I am getting blow error while running

ORA-08103: object no longer exists ---------------------------- Cannot read the next data row for the dataset 'DataSet1'.

My actual requirment is to populate the items in the tempary table  and use the temp table in the subqurey for IN clause.

The Sp is working fine in Toad.

Sobha

[v-mdharahman]

Hi @Sobha,

Thanks for reaching out to the Microsoft fabric community forum.

As @johnbasha33 already responded to your query, please go through his response and check if it solves your issue.

I would also take a moment to thank @johnbasha33, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

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

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Sobha,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[Sobha]

Hi

But Idea of collections works.

My Issue is not really solved.I have some difficulty in populating the collection variable

Thanks 

[v-mdharahman]

Hi @Sobha,
Can you please confirm if its the original question you are struck with or where you able to move forward with the response provided by @johnbasha33. Also if you were able to move forward then what is the exact issue you are facing right now.

Best Regards,

Hammad.

[Sobha]

Hi,

 The Idea of Collection Works and I am able to populate the collection in the Stored procedure.

Now i want to call the procedure from from Power BI paginated report as dataset

 like below

DECLARE
sub_ids SYS_REFCURSOR;
xyz varchar2(100);

BEGIN
DORIS.XYZ_collections_select_test(
'2024',
NULL ,
:prSubscriber,
sub_ids );

DBMS_SQL.RETURN_RESULT(sub_ids);
END;

:prSubscriber is the parameter which accepts Multiple values of type text,

for example prSubscriber  = 1234, 678,15674

if i pass only one value(prSubscriber  = 1234) , i get the value back. if i pass more than one value( prSubscriber  = 1234, 678,15674), there is an error. ORA-01008: not all variables bound or 

ORA-06550: line 5, column 6:
PLS-00306: wrong number or types of arguments in call to 'XYZ_COLLECTIONS_SELECT_TEST'
ORA-06550: line 5, column 6:
PL/SQL: Statement ignored

I think, the "," in prSubscriber   is interpreted as set of parameters to the Procedure.

Any idea what is the workaround?

TIA

Sobha

[v-mdharahman]

Hi @Sobha,

To handle multiple values passed as a comma-separated string, you can modify your stored procedure to parse this string into individual elements and process them accordingly.

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

This function takes a comma-separated string and returns a collection (SYS.ODCIVARCHAR2LIST) of individual values.

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

In this procedure prSubscriber is the comma-separated string of subscriber IDs. "split_string" function is used to convert this string into a collection and the SELECT statement uses this collection to filter records where subscriber_id matches any of the provided IDs.

Calling the Procedure from Power BI Paginated Report:

When setting up your dataset in Power BI Paginated Report, ensure that the parameter :prSubscriber is defined as a multi-value parameter and the values selected are concatenated into a comma-separated string before being passed to the stored procedure.

For example, your query might look like:

DECLARE
  sub_ids SYS_REFCURSOR;
BEGIN
  DORIS.XYZ_collections_select_test(
    '2024',
    NULL,
    :prSubscriber,
    sub_ids
  );
  DBMS_SQL.RETURN_RESULT(sub_ids);
END;

Ensure that :prSubscriber is passed as a single string like '1234,678,15674'.

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

[Sobha]

Hi,

 I am stuck, for passing the value :prSubscriber from Power BI to Oracle procedure.

When i am passing the value to the Procedure it is giving error as 

there is an error. ORA-01008: not all variables bound or 

ORA-06550: line 5, column 6:
PLS-00306: wrong number or types of arguments in call to 'XYZ_COLLECTIONS_SELECT_TEST'
ORA-06550: line 5, column 6:
PL/SQL: Statement ignored.

:prSubscriber is a multivalue parameter and is ihave only one value, i get the data back. if more than 1 values are selected, it gives the above error

Basically, :prSubscriber is not going as a single parameter to the Oracle Procedure.

I feel I missign some steps here. Any suggetions

TIA

Sobha

[v-mdharahman · Accepted solution]

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

[Sobha]

Hi,

Thank you very much. The Logic is working. I am able to send the values to Backend ,insert to collections and use the collections in the Query instead of IN clause

Thank you

[v-mdharahman]

Hi @Sobha,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-mdharahman]

Hi @Sobha,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[johnbasha33]

@Sobha 

First, here’s what’s happening with your setup:

• In your stored procedure DORIS.XYZ, you are inserting into Sub_ids_list_xyz, which is a global temporary table (GTT).

• You then open a SYS_REFCURSOR selecting from the GTT and return it.

• ✅ In Toad or SQL Developer, this works perfectly because they maintain the same session for the insert and the select.

• ❌ But when calling it from Fabric, SSRS, Power BI, or even some external Oracle clients:

  • The Insert happens.

But when trying to Open Cursor and select, the temporary table no longer exists or is empty because a different session is used internally.

Thus ➔ the error:

ORA-08103: object no longer exists

appears.
(Not because the table "physically" vanished — but because the session's private copy of the table is gone.)

Avoid GTT completely inside the procedure. Use PL/SQL collections (like TABLE OF RECORDS)

Instead of inserting into a temp table, populate a PL/SQL collection (in memory) and open cursor directly from that.

Create a pipelined table function instead of a procedure

Use a Pipelined Function (PIPELINED) instead of a procedure ➔ that outputs rows directly without needing a temp table.

Use Global Temp Table but wrap everything inside a single procedure

Instead of using a cursor output, do the entire select and return result set inside a single session.
Meaning — avoid exposing REFCURSOR externally.

If your tool expects a simple query instead of SYS_REFCURSOR, it stays within one session and works.

Force session consistency using WITH HOLD cursors (if supported)

Some clients support WITH HOLD cursors to keep cursors open across transactions.
But Oracle itself doesn't natively support "WITH HOLD" like PostgreSQL does.

So this usually is NOT a clean solution in Oracle.

You could directly open cursor selecting from a CTE or subquery, like:

OPEN c_direct_reports FOR
SELECT '10002521' AS sub_id
FROM dual;

example:
CREATE OR REPLACE PROCEDURE DORIS.XYZ(
prBeginDate IN Varchar2 := '2024',
pGroupID IN Varchar2:= NULL ,
c_direct_reports OUT SYS_REFCURSOR)
AS
BEGIN
-- Instead of using temp table:
OPEN c_direct_reports FOR
SELECT '10002521' AS Sub_ID
FROM dual;
END;
/

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!