How to close a returning cursor in PL/SQL?
All you should need to do is issue a CLOSE on the cursor when you're done with it, regardless of where it was actually opened:
-- A function to return a SYS_REFCURSORCREATE OR REPLACE FUNCTION f_c RETURN SYS_REFCURSOR IS cur SYS_REFCURSOR;BEGIN OPEN cur FOR SELECT LEVEL FROM dual CONNECT BY LEVEL < 10; RETURN cur; END;Here's a sample run:
DECLARE cc SYS_REFCURSOR; r VARCHAR2(10);BEGIN cc := f_c; -- Get the cursor from the function LOOP FETCH cc INTO r; EXIT WHEN cc%NOTFOUND; dbms_output.put_line('Output is: '||r); END LOOP; CLOSE cc; -- Close the SYS_REFCURSOR returned from the functionEND;/Output is: 1Output is: 2Output is: 3Output is: 4Output is: 5Output is: 6Output is: 7Output is: 8Output is: 9As for returning a set of values from a function or procedure, here's another SO question on the topic.