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;