Live ORACLE

rem -----------------------------------------------------------------------
rem Filename:   varray.sql
rem Purpose:    Demontrate VARRAY (variable array in one database column) 
rem             collection types
rem Date:       12-Aug-2001
rem Author:     Frank Naude, Oracle FAQ
rem -----------------------------------------------------------------------

CREATE OR REPLACE TYPE vcarray AS VARRAY(10) OF VARCHAR2(128);
/
CREATE TABLE varray_table (id number, col1 vcarray);

INSERT INTO varray_table VALUES (1, vcarray('A'));
INSERT INTO varray_table VALUES (2, vcarray('B', 'C'));
INSERT INTO varray_table VALUES (3, vcarray('D', 'E', 'F'));

SELECT * FROM varray_table;
SELECT * FROM USER_VARRAYS;
-- SELECT * FROM USER_SEGMENTS;

-- Unnesting the collection:
select t1.id, t2.COLUMN_VALUE
from   varray_table t1, TABLE(t1.col1) t2
/

-- Use PL/SQL to access the varray...
set serveroutput on
declare
  v_vcarray vcarray;
begin
  for c1 in (select * from varray_table) loop
      dbms_output.put_line('Row fetched...');
      FOR i IN c1.col1.FIRST..c1.col1.LAST LOOP
          dbms_output.put_line('...property fetched: '|| c1.col1(i));
      END LOOP;
  end loop;
end;
/

-- Clean-up...
DROP TABLE varray_table;
DROP TYPE vcarray;

VARRAY