Solution: The Collection in the Collection

The crucial clues to solve the task I found at Adrian Billington’s Blog. It’s an older post, but still valid: introduction to bulk PL / SQL Enhancements in 9i .
The code below should be self explaining with the notes.
To show how to the data, I have added an output via DBMS_OUTPUT.

— First you have to create the type for nested collection

CREATE OR REPLACE type emp_t
AS
  object
  (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(10),
    SAL  NUMBER(7,2),
    COMM NUMBER(7,2) 
  )
/
create or replace TYPE tbl_emp  AS TABLE OF emp_t
/

 

DECLARE
  CURSOR c1
  IS
    SELECT deptno,
      dname,
      CAST (MULTISET( SELECT empno,ename,sal, comm FROM emp e WHERE e.deptno= d.deptno
                     ) AS tbl_emp  
           ) as emps -- I need an alias to be able to refer the embedded Collection
  FROM dept d;

  TYPE tbl_dept IS TABLE OF c1%ROWTYPE;
  depts tbl_dept ;
BEGIN
   OPEN c1;
   FETCH c1 BULK COLLECT INTO depts;
   CLOSE c1;
   FOR i IN 1..depts.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE(depts(i).deptno);
      FOR j IN  1.. depts(i).emps.COUNT
      LOOP
        DBMS_OUTPUT.PUT_LINE('***'||depts(i).emps(j).ename);
      END LOOP;
   END LOOP;
END;
/