How to close a returning cursor in PL/SQL?

Question

I am new to PL/SQL and I just got to cursors in my learning process. I've been seeing stored procedure parameters with type OUT SYS_REFCURSOR which, as far as I understand, its purpose is to 'return data' just like a pointer in C language. I been wondering who is going to close such SYS_REFCURSOR parameter if the procedure has to open it and can't close it? (If the procedure closes the out OUT SYS_REFCURSOR then there will not be any data to return).

Also, I think, it is bad design to rely on external functions other than the stored procedure that contains OUT SYS_REFCURSOR parameter to close the cursor. Is there a way I can return a table from a stored procedure without using cursors?

Answer 1

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_REFCURSOR
CREATE 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 function
END;
/

Output is: 1
Output is: 2
Output is: 3
Output is: 4
Output is: 5
Output is: 6
Output is: 7
Output is: 8
Output is: 9

As for returning a set of values from a function or procedure, here's another SO question on the topic.