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

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

Let's explore each of these in detail.

PLSQL 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 PLSQL Collection

Unlike associative arrays, an uninitialized VARRAY is null (undefined) rather than empty and has to be initialized 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 PLSQL 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 PLSQL 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 PLSQL 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

To learn more about PLSQL or other aspects of Oracle see Smartsoft for instructor-led PLSQL training in the UK.