oracle-databaseplsqlref-cursor

Returning a ref cursor from a Oracle Function


I am getting the error - PLS-00382 Expression is of wrong type.
I want to get the ref cursor as output. please let me know how can I do this

create or replace function  test_cur
return sys_refcursor
as
  var_ref sys_refcursor;
begin
  open var_ref for
  select item,status
    from item_master  
   where rownum <10;
  return var_ref;
end;


declare
  l_var sys_refcursor;
  l_item varchar2(100);
  l_status varchar2(10);
begin
  l_var:=test_cur;
  open l_var;
  loop
    fetch l_var into  l_item,l_status;
    exit when l_var%notfound;
    dbms_output.put_line(l_item||','||l_status);
  end loop;
end;

Can anybody please help me resolving this issue?


Solution

  • A couple of mistakes, look into my working example, just changed the source table:

    CREATE OR REPLACE FUNCTION  TEST_CUR RETURN SYS_REFCURSOR
    AS
       VAR_REF SYS_REFCURSOR;
    BEGIN
        OPEN VAR_REF FOR
            SELECT *
            FROM DUAL;
    
        RETURN VAR_REF;
    END;
    

    Here you don't need to open the cursor, it is already opened.

    DECLARE
        L_VAR SYS_REFCURSOR;
        L_STATUS VARCHAR2(10);
    BEGIN
        L_VAR:=TEST_CUR;
        LOOP
            FETCH L_VAR INTO L_STATUS;
            EXIT WHEN L_VAR%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE( L_STATUS );
        END LOOP;
        CLOSE L_VAR;
    END;
    

    A really interesting post showing how to test oracle cursors:

    5 different ways to test Oracle Ref Cursor results