Purpose of using different types of PL/SQL collections in Oracle

Question

What is the main purpose of using collections in oracle ?

1. Index by tables

2. Nested tables

3. Variable size ARRAY

Can you please explain the difference between the above types of collections ?

Answer 1

Let's start with Nested Tables, they are the most common form of collection and so represent a useful basis of comparison.

A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

type emp_nt is table of emp%rowtype;
emp_rec_nt emp_nt;

They are useful whenever we want to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

select * 
bulk collect into emp_rec_nt
from employees;

This gives us a source of data we can loop round; crucially we can navigate backwards as well as forwards, even skip to the end or the beginning, which are things we cannot do with a cursor. Nested tables can be collections of any data type, including composites such as PL/SQL records or user-defined types.

An Index By table is better called (as the docs do) an Associative Array . These are simple collections of single attributes with an index. Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value. So they are useful for caching data values for later use. The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

type emp_sal_aa is table of emp.sql%type
     index by emp.empno%type;
l_emp_sales emp_sal_aa;

Note that I could have declared that array using INDEX BY BINARY_INTEGER but it is clearer to use the %TYPE syntax instead (self-documenting code). Elements of that array can identified by an index value, in this case EMPNO:

l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.

Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading: they are actually fixed arrays. There's little we can do with VArrays which we can't do with nested tables (except constrain the number of elements and it's pretty rare that we would want to do that). They are declared like this:

type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;

We can use bulk collect to populate a VArray ...

select * 
bulk collect into emp_rec_va
from employees;

However we must be certain the query will return at most the number of elements specified in the VArray's declaration. Otherwise the SELECT will hurl ORA-22165.

There are no known use cases for variable arrays. Okay that's a bit harsh, but almost all of the time you will use nested tables instead. The one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.

The PL/SQL documentation devotes an entire chapter to collections. Find out more.