Forum Discussion

Sobha's avatar
Sobha
Frequent Visitor
1 year ago
Solved

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    CREAT...
SYS_REFCURSOR for the dataset
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

johnbasha33's avatar
johnbasha33
Super User
1 year ago

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