Forum Discussion
Getting data using Procedure by using the SYS_REFCURSOR.
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
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
12 Replies
First, here’s what’s happening with your setup:
-
In your stored procedure
DORIS.XYZ, you are inserting intoSub_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 existsappears.
(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 !!
-
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 TeamIf this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
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.Hi
But Idea of collections works.
My Issue is not really solved.I have some difficulty in populating the collection variable
Thanks
