Lab 18.2 Varrays

Lab Objective

After this Lab, you will be able to:

  • Use Varrays

As mentioned earlier, a varray is another collection type, and it stands for variable-size arrays. Similar to PL/SQL tables, each element of a varray is assigned a consecutive subscript starting at 1, as shown in Figure 18.2.

Figure 18.2. Varray


Figure 18.2 shows a varray consisting of five integer numbers . Each number is assigned a unique subscript that corresponds to its position in the varray.

It is important to note that a varray has a maximum size. In other words, a subscript of a varray has a fixed lower bound equal to 1, and an upper bound that is extensible if such a need arises. In Figure 18.2, the upper bound of a varray is 5, but it can be extended to 6, 7, 8, and so on up to 10. Therefore, a varray can contain a number of elements, varying from zero (empty array) to its maximum size. You will recall that PL/SQL tables do not have a maximum size that must be specified explicitly.

The general syntax for creating a varray is as follows (the reserved words and phrases surrounded by brackets are optional):

 TYPE  type_name  IS {VARRAY  VARYING ARRAY} (size_limit) OF  element_type  [NOT NULL];  varray_name  TYPE_NAME; 

First, a varray structure is defined using the TYPE statement, where TYPE_NAME is the name of the type that is used in the second step to declare an actual varray. Notice that there are two variations of the type, VARRAY and VARYING ARRAY. A SIZE_LIMIT is a positive integer literal that specifies the upper bound of a varray. Just like in the case of PL/SQL tables, there are restrictions that apply to an ELEMENT_TYPE of a varray. These restrictions are listed in Oracle help available online. Second, the actual varray is declared based on the type specified in the first step.

Consider the following code fragment:


 DECLARE    TYPE last_name_type IS VARRAY(10) OF student.    last_name%TYPE;    last_name_varray last_name_type; 

In this example, type last_name_type is declared as a varray of ten elements based on the column LAST_NAME of the STUDENT table. Next , the actual varray last_name_varray is declared based on the LAST_NAME_TYPE.

Similar to nested tables, a varray is automatically NULL when it is declared and must be initialized before its individual elements can be referenced. So consider a modified version of the example used in the previous lab. Instead of using nested table, this version uses varray (changes are highlighted in bold).


 DECLARE    CURSOR name_cur IS       SELECT last_name         FROM student        WHERE rownum <= 10;  TYPE last_name_type IS VARRAY(10) OF student.   last_name%TYPE;   last_name_varray last_name_type := last_name_type();  v_counter INTEGER := 0; BEGIN    FOR name_rec IN name_cur LOOP       v_counter := v_counter + 1;       last_name_varray.EXTEND;       last_name_varray(v_counter) := name_rec.last_name;       DBMS_OUTPUT.PUT_LINE ('last_name('v_counter'): '          last_name_varray(v_counter));    END LOOP; END; 

This example produces the following output:

  last_name(1): Crocitto   last_name(2): Landry   last_name(3): Enison   last_name(4): Moskowitz   last_name(5): Olvsade   last_name(6): Mierzwa   last_name(7): Sethi   last_name(8): Walter   last_name(9): Martin   last_name(10): Noviello   PL/SQL procedure successfully completed.  

Based on the preceding example, you may realize that collection methods seen in the previous lab can be used with varrays as well. Consider the following example, which illustrates the use of various collection methods when applied to a varray.


 DECLARE    TYPE varray_type IS VARRAY(10) OF NUMBER;    varray varray_type := varray_type(1, 2, 3, 4, 5, 6); BEGIN    DBMS_OUTPUT.PUT_LINE ('varray.COUNT = 'varray.COUNT);    DBMS_OUTPUT.PUT_LINE ('varray.LIMIT = 'varray.LIMIT);    DBMS_OUTPUT.PUT_LINE ('varray.FIRST = 'varray.FIRST);    DBMS_OUTPUT.PUT_LINE ('varray.LAST = 'varray.LAST);    varray.EXTEND(2, 4);    DBMS_OUTPUT.PUT_LINE ('varray.LAST = 'varray.LAST);    DBMS_OUTPUT.PUT_LINE ('varray('varray.LAST') = 'varray(varray.LAST));    -- Trim last two elements    varray.TRIM(2);    DBMS_OUTPUT.PUT_LINE('varray.LAST = 'varray.LAST); END; 

Consider the output returned by the example:

  varray.COUNT = 6   varray.LIMIT = 10   varray.FIRST = 1   varray.LAST = 6   varray.LAST = 8   varray(8) = 4   varray.LAST = 6   PL/SQL procedure successfully completed.  

The first two lines of output

  varray.COUNT = 6   varray.LIMIT = 10  

show the results of the COUNT and LIMIT methods, respectively. You will recall that the COUNT method returns the number of elements that a collection contains. The collection has been initialized to six elements, so the COUNT method returns a value of 6.

The next line of output corresponds to another collection method, LIMIT. This method returns the maximum number of elements that a collection can contain and is usually used with varrays only because varrays have an upper bound specified at the time of declaration. The collection VARRAY has an upper bound of ten, so the LIMIT method returns a value of 10. When used with nested tables, the LIMIT method returns NULL because nested tables do not have a maximum size.

The third and fourth lines of the output

  varray.FIRST = 1   varray.LAST = 6  

show the results of the FIRST and LAST methods.

The fifth and six lines of the output

  varray.LAST = 8   varray(8) = 4  

show the results of LAST method and the value of the eighth element of the collection after the EXTEND method increased the size of the collection. Notice that the EXTEND method

  varray.EXTEND(2, 4);  

appends two copies on the fourth element to the collection. As a result, the seventh and eighth elements both contain a value of 4.

Next, the last line of output

  varray.LAST = 6  

shows the value of the last subscript after the last two elements were removed via the TRIM method.


It is important to note that you cannot use the DELETE method with a varray to remove its elements. Unlike PL/SQL tables, varrays are dense, and using the DELETE method causes an error, as illustrated in the following example:

 DECLARE    TYPE varray_type IS VARRAY(3) OF CHAR(1);    varray varray_type := varray_type('A', 'B', 'C'); BEGIN    varray.DELETE(3); END;  varray.DELETE(3);   *   ERROR at line 6:   ORA-06550: line 6, column 4:   PLS-00306: wrong number or types of arguments in call to 'DELETE'   ORA-06550: line 6, column 4:   PL/SQL: Statement ignored  

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: