To handle closing of sysrefcursor returned from a Plsql Stored Procedure

Question

I have a stored procedure defined as below:

Create or replace Stored Procedure demo(Id in number, emp_dtl our sys_refcursor)

Begin

Open emp_dtl for
Select* from emp where empid=p_id;

End;

The above stored procedure is directly called by an API. While calling this Stored procedure we are receiving ORA-01002: fetch out of sequence and ORA-01001: INVALID CURSOR. we are receiving this error intermittently. I googled the error and looks like we get this error when the cursor closed before we fetch from the cursor. Can somebody please help me to understand how can we resolve this.

Answer 1

These errors can occur when the cursor is closed or if you try to fetch from a cursor that has already been closed. To resolve this issue, you need to ensure that the cursor is being handled properly.

This is the correction of your code:

CREATE OR REPLACE PROCEDURE demo(p_id IN NUMBER, emp_dtl OUT SYS_REFCURSOR) AS
BEGIN
    OPEN emp_dtl FOR
        SELECT * FROM emp WHERE empid = p_id;

    -- Fetch data from the cursor
    -- Note: You should FETCH INTO variables here if you want to retrieve data

    -- Close the cursor when done
    CLOSE emp_dtl;
EXCEPTION
    WHEN OTHERS THEN
        -- Handle any exceptions here (e.g., log the error)
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/