VARRAY

VARRAY is an Oracle data type used to support columns containing multivalued attributes, in this case, columns that can hold a bounded array of values.

Examples

Create a table with VARRAY column:

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

Insert data into table:

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'));
COMMIT;

Select data from table (unnesting the collection):

SQL> SELECT t1.id, t2.column_value
  2  FROM varray_table t1, TABLE(t1.col1) t2;
        ID COLUMN_VAL
---------- ----------
         1 A
         2 B
         2 C
         3 D
         3 E
         3 F
6 rows selected.

Extract data with PL/SQL:

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;
/

The output Like This:

Row fetched...
...property fetched: A
Row fetched...
...property fetched: B
...property fetched: C
Row fetched...
...property fetched: D
...property fetched: E
...property fetched: F
PL/SQL procedure successfully completed.

Monitor

A list of tables containing VARRAY columns can be obtained by querying USER_VARRAYS view.

Also see

• NESTED TABLE

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#