title: How to return a cursor populated with NESTED RECORD type from Oracle procedure
body: <p>I need to return a <strong>cursor</strong> from Oracle procedure, the cursor has to contain RECORD types. I know how to solve this problem when the RECORD is simple, but how can this be solved for <strong>NESTED RECORDS</strong>?</p>
  
  <p>There is a working block of code for simple RECORD:  </p>
  
  <pre><code>-- create package with a RECORD type
  create or replace package pkg as
      -- a record contains only one simple attribute
      type t_rec is RECORD (
          simple_attr number
      );
  end;
  /
  
  -- create a testing procedure
  -- it returns a cursor populated with pkg.t_rec records
  create or replace procedure test_it(ret OUT SYS_REFCURSOR) is
      type cur_t is ref cursor return pkg.t_rec;
      cur cur_t;
  begin
      -- this is critical; it is easy to populate simple RECORD type,
      -- because select result is mapped transparently to the RECORD elements
      open cur for select 1 from dual;
      ret := cur;  -- assign the cursor to the OUT parameter
  end;
  /
  
  -- and now test it
  -- it will print one number (1) to the output
  declare
      refcur SYS_REFCURSOR;
      r pkg.t_rec;
  begin
      -- call a procedure to initialize cursor
      test_it(refcur);
      -- print out cursor elements
      loop
          fetch refcur into r;
          exit when refcur%notfound; 
          dbms_output.put_line(r.simple_attr);
      end loop;  
      close refcur;
  end;
  /
  </code></pre>
  
  <p>Can you show me, <strong>how it could be done when a RECORD t_rec contains NESTED RECORD</strong>?</p>
  
  <p>Modify the example in the folowing way:</p>
  
  <pre><code>-- create package with a NESTED RECORD type
  create or replace package pkg as
      type t_rec_nested is RECORD (
          nested_attr number
      );
      -- a record with NESTED RECORD 
      type t_rec is RECORD (
          simple_attr number,
          nested_rec t_rec_nested
      );
  end;
  /
  
  create or replace procedure test_it(ret OUT SYS_REFCURSOR) is
      type cur_t is ref cursor return pkg.t_rec;
      cur cur_t;
  begin
      -- how to populate a result?
      open cur for ????
      ret := cur;
  end;
  /
  </code></pre>
  
  <p>The question is how to modify test_it procedure to populate a cursor? 
  I spent many hours searching the solution, I will appreciate any help.</p>