Returning a ref cursor from a Oracle Function

Question

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?

Make sure you don't have `DBMS_OUTPUT` in your production code. — Lalit Kumar B, Sep 17 '14 at 15:40

gustavodidomenico · Accepted Answer · 2020-02-12T13:36:53.303

7

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

edited Feb 12 '20 at 13:36

answered Sep 17 '14 at 12:57

gustavodidomenico

4,640
1
34
50

Hopefully, the `DBMS_OUTPUT ` statement doesn't move to production. – Lalit Kumar B Sep 17 '14 at 15:40
1

Where should we close the cursor? – Toufiq - Oracle APEX Jan 24 '20 at 13:37
In this case, Oracle will close the cursor as soon as it is out of scope. However, I must say that it should be a good habit always to close it explicitly. Let me update the answer. – gustavodidomenico Feb 12 '20 at 13:35

score 0 · Answer 2 · answered Aug 01 '20 at 23:46

0

declare
  l_var sys_refcursor;
  l_item varchar2(100);
  l_status varchar2(10);
begin
  l_var:=test_cur;
  --open l_var; this is already opened in the fuction.
  loop`enter code here`
    fetch l_var into  l_item,l_status;
    exit when l_var%notfound;
    dbms_output.put_line(l_item||','||l_status);
  end loop;
end;

answered Aug 01 '20 at 23:46

user14035155

1

Welcome to Stack Overflow. Code-only answers are discouraged on Stack Overflow because they don't explain how it solves the problem. Please edit your answer to explain what the code does and how it fixes the issues in the question, so that it is also useful for other users with a similar problem. – FluffyKitten Aug 02 '20 at 05:36

Returning a ref cursor from a Oracle Function

2 Answers2

Linked