PL/SQL Collections (Part 2 - VARRAYs)

PLSQL collections come in to play once you've been using PL/SQL for a while and  realize that there is a need for more complex data types such as the array, table and set types that exist in other languages. Oracle PLS/QL has 3 types of collections: 

  1. associative arrays (also known as index-by tables)
  2. VARRAY (a varying size array)
  3. nested tables

Let's explore each of these in detail.

PL/SQL VARRAY Collections

The VARRAY (variable-size array) type is the equivalent of array types in other languages. Like associative arrays, they are suitable for smallish look-up tables but unlike associative arrays the MAXIMUM number of elements

in the array must be specified in the declaration. They are known as variable-size arrays because although the maximum size of the array is fixed, the actual size changes as elements are added and removed. 

Initializing a VARRAY PL/SQL Collection

Unlike associative arrays, an uninitialised VARRAY is null (undefined)  rather than empty and has to be initialised using a constructor. The index for VARRAYs always start from 1, so a VARRAY with 10 elements goes from 1 to 10 and they are always densely populated. 

Let's look at an example.

First we declare the type, then we declare a variable of that type.

DECLARE

TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20);
--have to declare a type.
--these type declarations define our plsql collections

colours colour_tab; -- a plsql collection variable

BEGIN

    colours  := colour_tab('RED','WHITE','BLUE');
                                 --VARRAY fully initialized

END;

Accessing elements in a VARRAY PL/SQL Collection

Reading from and writing to a VARRAY is done in the same way as with nested tables. First of all we have to initialise the array (as shown in the preceding example), then we can insert data into and retrieve data from the varray.


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.


DECLARE
    TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20);
    colours colour_tab; -- a plsql collection variable
BEGIN
    colours  := colour_tab('RED','WHITE','BLUE');
    colours(2) := 'GREEN'; -- overwrite 2nd element
    DBMS_OUTPUT.PUT_LINE('first colour = '||colours (1));
END;

/
first colour = RED

As with nested tables, if you attempt to read from or write to an un-initialized varray, Oracle raises an exception as in the following example.

DECLARE
    TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20);
    colours colour_tab; -- a plsql collection variable
BEGIN
    DBMS_OUTPUT.PUT_LINE('first colour = '||colours (1));
END;

declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 5

Oracle also raises an exception if we attempt to access an element in a  collection beyond its current size when it has only been partially initialized or elements have been deleted.

DECLARE
    TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20);
    colour colour_tab := colour_tab('GREEN');
BEGIN
    DBMS_OUTPUT.PUT_LINE('2ND colour = '||colour (2));
           -- invalid attempt to read cell 2 which doesn't exist
END;

/
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 5

Changing the size of a VARRAY PL/SQL Collection

We've said that VARRAYs are variable-sized arrays, so how do we change the size? Simple we use the DELETE and TRIM methods to reduce the size and the EXTEND method to increase the size as in the following example.


DECLARE
    TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20);
    french_colours colour_tab := 
            colour_tab('RED','WHITE', 'BLUE')
    spanish_colours colour_tab; -- a plsql collection variable

BEGIN
    spanish_colours colour_tab := colour_tab('RED','YELLOW','RED');
    spanish_colours.TRIM(2);  --remove last 2 elements
    spanish_colours.TRIM;     --remove last element

    french_colours.DELETE;--remove ALL elements
    french_colours.EXTEND;    -- add one element to end
    french_colours.EXTEND(3); -- attempt to add another 3 cells 
       --causes ORA-06532 exception

END;
/
declare
*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at line 13

Note that if you attempt to increase the size of the VARRY collection beyond its maximum declared size, Oracle raises an exception "ORA-06533: Subscript beyond count". This also happens if you attempt to reduce the size of the VARRAY to less than zero!

Other VARRAY PL/SQL Collection Methods

We've already seen the TRIM,DELETE and EXTEND methods. The other ones applicable to VARRAYs are:

  • COUNT: This function returns the number of elements (cells) in the collection, 0 if the collection is empty
  • LAST: For an initialized empty VARRAY, LAST is NULL. For an initialized non-empty VARRAY it returns the same value as COUNT.
  • FIRST: For an initialized empty VARRAY, FIRST is NULL. For an initialized non-empty VARRAY, FIRST always returns 1
  • EXISTS: Returns true or false depending on whether or not the specified element exists (and won't cause an exception if the VARRAY has not been initialized)
  • LIMIT: Returns the maximum size of the VARRAY 
  • PRIOR and NEXT: These functions return the index of the previous and next cells in the VARRAY. At the last element in the array, NEXT returns NULL and at the first element, PRIOR returns NULL

Return to part 1 - associative arrays  Continue with PL/SQL collection part 3


Looking for Oracle PL/SQL training? Learn PL/SQL from the experts and sky-rocket your career - see here for details of our Oracle training.

Return from PL/SQL collections to PLSQL overview
Return to home page