“The beginning of knowledge is the discovery of something we do not understand.”
Frank Herbert.

Collections are single-dimensioned lists of information, very similar to 3GL arrays. Much like ‘in-memory’ tables. Maybe that’s why they started out as PL/SQL Tables. The PL/SQL only version got renamed a couple of times. When first introduced in Oracle 7 they were called PL/SQL tables. In Oracle 8i they were renamed to Index By tables. Probably because of the index by clause in the syntax. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table did, but it can now be indexed by a VARCHAR2 as well.

Oracle 8i introduced 2 new collection types, the Nested Table and the VARRAY. Most notable difference between these two types and the existing (but renamed) index-by table type was that these new collection types exist in both SQL and PL/SQL where the index-by table still only exists in PL/SQL.
Let’s take a look at the different collection types individually and then afterwards compare them.

Associative Array

The syntax for the associative array is like this:

After an associative array type is declared it can be used to declare a variable based on this type much like a variable based on a base type (VARCHAR2, NUMBER, DATE):

To add a value to the AA all you have to do is tell the PL/SQL Engine to put a value at a certain index, like this:

The PL/SQL engine does all the heavy lifting for you. What heavy lifting, you might ask, well you’ll see what you have to do when using one of the other types.

Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements. Unbounded, well almost unbounded. When indexing by integer you can use indexes ranging from -2**31 .. 2**31, aka -2,147,483,647 to 2,147,483,647. I think you will never fill up all the elements in an array. Your database will probably run out of memory before you have created all elements. If this still isn’t enough of you want a different way of indexing then you can always switch to varchar2 as a key type.

Associative arrays can be sparse, which means not all elements between two elements need to be defined. You can for instance have an element at index -29 and one at index 12 with nothing in between. Homogeneous elements mean every element must be of the same type. If you define the array to be of type X then all the elements of that array must be of type X.

Nested Table

The syntax for defining a nested table in SQL is like this:

The syntax for defining a nested table in PL/SQL is like this:

After it has been defined in SQL the nested table can be used as a column in the table definition, just like you would use one of the simple types.

Note that you can define how you want the data in the nested table to be stored. Data in the nested table is stored out-of-line, i.e. separately from the rest of the data in the table.
But you can also use it as a variable type in PL/SQL.

For the PL/SQL compiler it makes no difference whether the nested table is defined as a PL/SQL type or as an SQL object.
To add a value to the nested table you first have to create it using the constructor.

After creation (or if the nested table already existed) you have to make room for the element you want to add, by extending the nested table:

Now that there is room for the element we can put the value in the nested table.

Nested tables are also single-dimensional, unbounded collections of homogenous elements (like the associative arrays). They start of dense, but they can become sparse through deletions. Nested tables do not maintain the order of the elements in the table.

There are a couple of exceptions defined which you might encounter. If you try to reference a collection, but you didn’t initialize it you will get:

If you try to assign a value to an index which has not been created yet, using the extend function on the Nested Table, you will get:

Continue to Collections in Oracle Part 2

Ref:
www.developer.com/db/article.php/3379271/Oracle-Programming-with-PLSQL-Collections
www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-Nested-Tables
Oracle PL/SQL Programming 5th Edition – Steven Feuerstein
Oracle PL/SQL for DBA’s – Arup Nanda and Steven Feuerstein