Live ORACLE

-- Cleanup before Creating
DROP TABLE depts CASCADE CONSTRAINTS PURGE
/

DROP TYPE dnames_var
/

-- Creating ........
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/

CREATE TABLE depts (
 region     VARCHAR2(25),
 dept_names DNAMES_VAR)
/

BEGIN
 INSERT INTO depts
 VALUES     ('Europe',
             Dnames_var('Shipping','Sales','Finance'));
 INSERT INTO depts
 VALUES     ('Americas',
             Dnames_var('Sales','Finance','Shipping'));
 INSERT INTO depts
 VALUES     ('Asia',
             Dnames_var('Finance','Payroll','Shipping','Sales'));
 COMMIT;
END;
/

DECLARE
 TYPE dnames_tab IS TABLE OF DNAMES_VAR;
 v_depts  DNAMES_TAB;
BEGIN
 SELECT dept_names
 BULK COLLECT INTO v_depts
 FROM   depts;
 dbms_output.Put_line(v_depts.COUNT); -- prints 3

END;
/ 

Using BULK COLLECT with Nested Tables