Collections

I am still reading "Oracle Advanced PL/SQL Professional Guide". This book really stresses the different types of collections. So let's get down to business. Collections contain elements of the same type. They are stored in memory in either the PGA or SGA. Some types can also be stored in the database. Here are the collections Oracle has to offer:
  1. Associative Arrays
  2. Nested Tables
  3. VARRAYs
Associative arrays were originally called PL/SQL tables. Then they were called index by tables. Now I guess they are associative arrays. These I used to use a lot in data correction scripts. Not so much any more. You index the array by either a number or string. It can be a sparse collection. You define a type for the associative array like TYPE my_assoc_type IS TABLE OF VARCHAR2(4) INDEX ?BY BINARY_INTEGER.

Next you have nested tables. Indexes are numeric and start with 1. These start out as dense collections. But they could get sparse if you DELETE an item. This type of collection can be stored in a database. When you do that, the data for columns of this type is stored out of line. You can initialize the collection with a constructor. And you can define a collection of this type like TYPE my_nested_type IS TABLE OF VARCHAR2(4).

The VARRAY is like the nested table. It also has numeric indexes which start at 1. You can store collections of this type in a table. Unlike nested arrays, columns of this type in a database table have the collection stored inline. The result is better performance than nested tables in database tables. This collection type is always dense. While you can DELETE from a VARRAY, you must DELETE all items in the VARRAY. You can define a collection of this type like TYPE my_varray_type IS VARRAY(10) OF VARCHAR2(4).

Here are some functions that work on collections:
  • EXISTS
  • COUNT
  • FIRST
  • LAST
  • PRIOR
  • NEXT
  • EXTEND (nested table or VARRAY only)
  • TRIM (nested table or VARRAY only)
  • DELETE