Lesson 1

As you saw in previous lessons in this course, a collection is an ordered group of elements that are all of the same type. The two types of collections that are defined within Oracle are

1. varrays and
2. nested tables.

Collections can store instances of an object type, be attributes of an object type, and also be parameters to other collections. You can use collections to move data in and out of the database or between client-side applications and stored subprograms. In addition, you can define collection types within a PL/SQL package and use them programmatically within your applications.
A collection consists of an inner table and an outer table. The inner table refers to the enclosing table in which you use a nested table or varray as a data type for a column. The outer table, also known as a nested table, refers to the collection that is implemented as a column within a table. For example, within the schema, DETAIL_TABLE is the inner table, whereas the DETAIL_NEST column within the SALE_HEADER table is the outer table.

Collection types have been enhanced to provide better performance and greater functionality. You can now alter the size of a VARRAY type object, use varrays in temporary tables, and specify different tablespaces for different columns of nested table type. Nested Table type now also supports comparison conditions (for example, Equal, Not Equal, Member Of) and ANSI SQL 2003 Multiset operations (for example, Multiset Intersect, Multiset Union).

Records are necessary structures to manage single rows of data. Collections are necessary structures to manage multiple rows of data. The first collections were PL/SQL tables, introduced in the Oracle 7 database. Oracle 8 added two new types, both known as collections. PL/SQL tables were renamed index-by tables and collections let you store data sets in a row within a table. Oracle 8i improved the access and management of collections. Oracle 9i introduced multilevel collections and Oracle 10g improved collections by adding

1. ANSI functionality,
2. globalization,
3. multiset operations, and
4. unique string indexes for associative arrays.

Index-by tables were again renamed; now they are known as associative arrays.

Collections are lists, which may be ordered or unordered. Ordered lists are indexed by unique subscripts; unordered lists are indexed by unique identifiers, which may be numbers, hash values, or string names.

Oracle 10g provides three types of collections and none of these are technically new to Oracle 10g. However, you might argue that associative arrays are new because of the significant subscript changes. Instead of unique number subscripts, Oracle 10g allows you to use either a unique number or a string. These are key structures that you will use frequently in PL/SQL programming. You should take time to develop a clear understanding of what they are, when to use them, and how to use them. Each of the collection types will be explained and demonstrated. You should have a solid foundation for the discussion of collections. If you skipped the record and object type discussion, it is assumed you have command of those topics. The three Oracle 10g collection types and their descriptions are

1. Associative arrays (index-by tables)
2. Nested tables
3. Varrays

When you have completed this module, you will be able to:

1. Explain the concept of flattening a nested table
2. Describe the SQL enhancements for nested tables such as THE and TABLE
3. Query nested tables and varrays
4. Create a PL/SQL block to query nested tables and varrays

In the next lesson, we will define the concept of flattening a nested table.