Using VARRAYs : Varray « Collections « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Collections
3. Varray

In the Oracle environment, array subscripts start from 1, and not from 0 (as in C and Java).

VARRAYs are of fixed length.

You specify the length of the array when you define it.

Arrays of elements of the same type use sequential numbers as a subscript.

VARRAYS can be used both in PL/SQL and SQL.

You should use VARRAYs when you know the size of your data set and that size is very stable.

declare
  type VarrayType is varray(size) of ElementType;
...
create or replace type VarrayType is varray(size) of ElementType;

The size of a VARRAY must be a positive integer and cannot be null.

You cannot create an array of REF CURSORs.

SQL>
SQL> declare
  2      type month_va is varray(13) of VARCHAR2(20);
  3      v_month_va month_va;
  4      v_count_nr number;
  5  begin
  6      v_month_va:=month_va('A','B','C','D','E','F','G');
  7      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
  8
  9      v_month_va.extend;
 10      v_month_va(v_month_va.last):='Null';
 11      DBMS_OUTPUT.put_line('Length:'||v_month_va.count);
 12
 13      for i in v_month_va.first..v_month_va.last
 14      loop
 15          DBMS_OUTPUT.put_line('v_month_va(i): '||v_month_va(i));
 16      end loop;
 17  end;
 18  /
Length:7
Length:8
v_month_va(i): A
v_month_va(i): B
v_month_va(i): C
v_month_va(i): D
v_month_va(i): E
v_month_va(i): F
v_month_va(i): G
v_month_va(i): Null

PL/SQL procedure successfully completed.