Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Collections

 

Using VARRAYs

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.