Demonstrate VARRAY database types

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;