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.

[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 !!