Returning a Ref Cursor based on a PL/SQL Collection

The question on the OTN Forum was: How do i add rows to a pl/sql table and then use the table to open a ref cursor.. Somehow I was a little intrigued and decided to try to formulate a reply. The first part of the question, although asked in a somewhat unclear way, was obvious. The second part was more interesting. What I came up with – using the TABLE operator – was the following:
First create a type in the database that describes the collection that we want to open the ref cursor for:

create type num_tbl is table of number
/

Then create a function that will define and populate a nested table of this type, open a ref cursor for it and return that ‘handle’:

create or replace
function get_num_cursor
return sys_refcursor
is
  l_num_tbl num_tbl;
  l_idx integer;
  l_refcursor sys_refcursor;
begin
  l_num_tbl:= num_tbl(10,20);
  l_num_tbl.extend(5);
  l_num_tbl(l_num_tbl.last):= 30;
  l_num_tbl.extend;
  l_num_tbl(l_num_tbl.last):= 40;
  open l_refcursor for select * from table(cast (l_num_tbl as num_tbl));
  return l_refcursor;
end;
/

Selecting this function from dual in SQL*Plus shows the following result:

SQL> select get_num_cursor from dual
  2  /

GET_NUM_CURSOR
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

COLUMN_VALUE
------------
          10
          20




          30
          40

8 rows selected.

The next step is to make proper use this ref cursor in the client programs.