Oracle Function - Fetch From Returned ref_cursor - ORA-01001: invalid cursor

Question

I am having trouble fetching from an open ref_cursor in PL/SQL when it is returned from a function. When I put the exact same fetch statement in the body of the function in place of the return statement then it works fine.

function GetBound(p_lat in number, p_long in number) return ref_cursor
IS
v_rc_ref_cursor sys_refcursor;
BEGIN

open v_rc_ref_cursor for select * from state_bound;
return v_rc_ref_cursor;

END;

Now if I call it from an anonymous plsql block I get the error "ORA-01001: invalid cursor"

    DECLARE

    v_rc_ref_cursor sys_refcursor;
    v1 number(38);
    v2 varchar2(50);
    v3 number(38);
    v4 varchar2(50);
    BEGIN

    v_rc_ref_cursor := GetBound(122.0928,-18.6974);

    fetch v_rc_ref_cursor into v1, v2, v3, v4;
    close v_rc_ref_cursor;
    DBMS_OUTPUT.PUT_LINE(v1 || v2 || v3 || v4);

    END;

However if I put the anonymous block into the actual function it all works. See below:

function GetBound(p_lat in number, p_long in number) return ref_cursor
IS

v_rc_ref_cursor sys_refcursor;
v1 number(38);
v2 varchar2(50);
v3 number(38);
v4 varchar2(50);

BEGIN

open v_rc_ref_cursor for select * from state_bound;
-- return v_rc_ref_cursor;

fetch v_rc_ref_cursor into v1, v2, v3, v4;
close v_rc_ref_cursor;
DBMS_OUTPUT.PUT_LINE(v1 || v2 || v3 || v4);

END;

I have read around and found some examples of people doing what I'm doing here so to my knowledge this should work. eg. https://community.oracle.com/thread/888365

Could someone help me figure out what I'm doing wrong here?

Answer 1

The way I ended up solving this problem was changing the return type from ref_cursor to sys_refcursor. It seems like a silly way to fix it but it worked with that change. code:

function GetBound(p_lat in number, p_long in number) return sys_refcursor
IS
v_rc_ref_cursor sys_refcursor;
BEGIN

open v_rc_ref_cursor for select * from state_bound;
return v_rc_ref_cursor;

END;