Which collection type should I use

Question:
I need to pass a collection from one PL/SQL program to another. Which collection type should I use, or does it not make any difference?
Answer:
Oracle PL/SQL offers three types of collections (arraylike structures): associative array, nested table, and VARRAY. Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. I offer some guidance and a table that describes many of the differences between associative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. There are several reasons for this preference: At one time (Oracle Database 7.3), associative arrays—then called PL/SQL tables—were the only type of collection in PL/SQL. So I got used to using them. They also involve the least amount of coding: You don't have to initialize or extend them. In Oracle9i Database Release 2 and later, you can index associative arrays by strings as well as by integers. However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
The following guidelines will help you choose an associative array, nested table, or VARRAY:
• If you need a sparsely indexed list (for "data-smart" storage, for example), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable, but it is inefficient to do so for anything but the smallest collections.
• If your PL/SQL application requires negative subscripts, you have to use associative arrays.
• If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.
• If you want to enforce a limit on the number of rows stored in a collection, use VARRAYs.
• If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle Database will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.
• If you want to preserve the order of elements stored in the collection column and if your data set will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.
Here are some other indications that a VARRAY would be appropriate: You don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.