[SOLVED] Does Oracle close the SYS_REFCURSOR returned from a function which is used in a SELECT statement?

Does Oracle close the SYS_REFCURSOR returned from a function which is used in a SELECT statement?

I need to store some queries in the DB (inside functions in packages) and then call the functions from SQL Developer (from ORDS), so I found that you can return the queries in SYS_REFCURSORs from the stored functions like this:

CREATE OR REPLACE FUNCTION test RETURN SYS_REFCURSOR AS
  vRC SYS_REFCURSOR;
BEGIN
  OPEN vRC FOR SELECT  *
               FROM    employees
               WHERE   empid = 34650;

  RETURN vRC;
END;

And later on, simply retrieve the data in SQL Developer like this:

SELECT  test
FROM    dual;

So my question is...

Is this correct? I know that everytime we OPEN a cursor we need to explicitaly CLOSE it and every example of this that I have foud close the refcursor in PL/SQL and I need to get the data from a SELECT statement (in order to parse it to JSON in SQL DEVELOPER).

Also I have found that in SQLPlus the "print" statement closes the cursor once it fetched all the data, does the SELECT statement im my example do this as well?

Solution

You can get the cursor and print its contents like this:

VARIABLE cur REFCURSOR;
BEGIN
  :cur := test();
END;
/
PRINT cur;

The PRINT and the SELECT statements will both read the cursor and when they have read all the rows they will implicitly close the cursor. You cannot read the data from a cursor twice (however, you can call the function multiple times to get multiple cursors all containing the same information).