Returning a ref cursor from a Oracle Function
16,839
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
Author by
redsoxlost
Updated on June 14, 2022Comments
-
redsoxlost almost 2 years
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 thiscreate 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?
-
Lalit Kumar B over 9 yearsHopefully, the
DBMS_OUTPUT
statement doesn't move to production. -
TMohamme over 4 yearsWhere should we close the cursor?
-
gustavodidomenico about 4 yearsIn 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.
-
FluffyKitten almost 4 yearsWelcome 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.