Oracle PL/SQL Tutorial/Collections/Varray of Number

Accessing VARRAY elements

SQL> declare
  2    Type numberVarray is VARRAY(5)OF NUMBER;
  3    v_numvarray numberVarray;
  4  begin
  5    v_numvarray :=numberVarray(10,20,30,40);
  6
  7
  8    dbms_output.put_line(to_char(v_numvarray(1))||", "||to_char(v_numvarray(2))||", "||to_char(v_numvarray(3))||", "||to_char(v_numvarray(4)));
  9
 10    v_numvarray(4):=60;
 11
 12    dbms_output.put_line(to_char(v_numvarray(1))||", "||to_char(v_numvarray(2))||", "||to_char(v_numvarray(3))||", "||to_char(v_numvarray(4)));
 13  end;
 14  /
10, 20, 30, 40
10, 20, 30, 60
PL/SQL procedure successfully completed.
SQL>
SQL>

Defining a VARRAY

SQL>
SQL> DECLARE
  2
  3    TYPE numberVarray IS VARRAY(5)OF NUMBER;
  4
  5    v_numvarray numberVarray;
  6
  7  BEGIN
  8
  9    /*Do some processing */
 10
 11    null;
 12
 13  END;
 14  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Initializing a VARRAY

SQL>
SQL> DECLARE
  2
  3    TYPE numberVarray IS VARRAY(5)OF NUMBER;
  4
  5    v_numvarray numberVarray :=numberVarray(10,20,30,40);
  6
  7  BEGIN
  8
  9    dbms_output.put_line("The first element of v_numvarray is "|| TO_CHAR(v_numvarray(1)));
 10  END;
 11  /
The first element of v_numvarray is 10
PL/SQL procedure successfully completed.
SQL>
SQL>

Is a varray null value

SQL>
SQL> declare
  2    Type numberVarray is VARRAY(5)OF NUMBER;
  3
  4    v_numvarray1 numberVarray;
  5
  6  begin
  7
  8    if v_numvarray1 IS NULL then
  9
 10      dbms_output.put_line("v_numvarray1 is null");
 11
 12    end if;
 13
 14    if v_numvarray1(1) IS NULL then
 15
 16      dbms_output.put_line("The first element of v_numvarray1 is null");
 17
 18    end if;
 19
 20  end;
 21  /
v_numvarray1 is null
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 14

Multilevel Collections

SQL>
SQL> CREATE OR REPLACE TYPE varray_one IS VARRAY(10)OF NUMBER;
  2  /
Type created.
SQL> CREATE OR REPLACE TYPE varray_one_nested IS VARRAY(10)OF varray_one;
  2  /
Type created.
SQL> CREATE OR REPLACE TYPE varray_two_nested IS VARRAY(10)OF varray_one_nested;
  2  /
Type created.
SQL>
SQL> DECLARE
  2    v1 varray_one;
  3    v2 varray_one_nested;
  4    v3 varray_two_nested;
  5  BEGIN
  6    v1 :=varray_one(1,2,3);
  7
  8    v2 :=varray_one_nested(varray_one(11,12,13),varray_one(21,22,23));
  9
 10    v3 :=varray_two_nested(
 11      varray_one_nested(varray_one(111,112,113),varray_one(121,122,123)),
 12      varray_one_nested(varray_one(211,212,213),varray_one(221,222,223)));
 13
 14    for i in 1..v1.count loop
 15
 16      dbms_output.put_line(v1(i));
 17
 18    end loop;
 19
 20    for i in 1..v2.count loop
 21
 22      for j in 1..v2(i).count loop
 23
 24        dbms_output.put_line(v2(i)(j));
 25
 26      end loop;
 27
 28    end loop;
 29
 30    for i in 1..v3.count loop
 31
 32      for j in 1..v3(i).count loop
 33
 34        for k in 1..v3(i)(j).count loop
 35
 36          dbms_output.put_line(v3(i)(j)(k));
 37
 38        end loop;
 39
 40      end loop;
 41
 42    end loop;
 43
 44  END;
 45  /
1
2
3
11
12
13
21
22
23
111
112
113
121
122
123
211
212
213
221
222
223
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop type varray_one force;
Type dropped.
SQL>
SQL> drop type varray_one_nested force;
Type dropped.
SQL>
SQL> drop type varray_two_nested force;
Type dropped.
SQL>
SQL>

NULL VARRAYS and NULL Elements

SQL>
SQL> declare
  2    Type numberVarray is VARRAY(5)OF NUMBER;
  3    v_numvarray numberVarray;
  4  begin
  5
  6    if v_numvarray IS NULL then
  7      dbms_output.put_line("v_numvarray is null");
  8    end if;
  9
 10    v_numvarray :=NULL;
 11
 12    if v_numvarray IS NULL then
 13      dbms_output.put_line("v_numvarray is null after assignment");
 14    end if;
 15  end;
 16  /
v_numvarray is null
v_numvarray is null after assignment
PL/SQL procedure successfully completed.
SQL>

Pass NULL value to varray

SQL>
SQL> declare
  2    Type numberVarray is VARRAY(5)OF NUMBER;
  3    v_numvarray2 numberVarray;
  4  begin
  5    v_numvarray2 :=numberVarray(NULL);
  6
  7    if v_numvarray2 IS NULL then
  8
  9      dbms_output.put_line("v_numvarray2 is null");
 10
 11    else
 12
 13      dbms_output.put_line("v_numvarray2 is not null");
 14
 15    end if;
 16
 17    if v_numvarray2(1) IS NULL then
 18
 19      dbms_output.put_line("The first element of v_numvarray2 is null");
 20
 21    end if;
 22  end;
 23  /
v_numvarray2 is not null
The first element of v_numvarray2 is null
PL/SQL procedure successfully completed.
SQL>
SQL>