Oracle PL/SQL Tutorial/Collections/Varray of Number

Accessing VARRAY elements

   <source lang="sql">

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

Defining a VARRAY

   <source lang="sql">

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

Initializing a VARRAY

   <source lang="sql">

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

Is a varray null value

   <source lang="sql">

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

Multilevel Collections

   <source lang="sql">

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

NULL VARRAYS and NULL Elements

   <source lang="sql">

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

Pass NULL value to varray

   <source lang="sql">

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