René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Collections in PL/SQL

In PL/SQL a collection variable is a variable that can store zero, one ore more elements of a specific type (either an internal data type or a user defined data types). The type of the variable is itself a user defined type. Since a collection is a user defined type, a collection type can store collections as well.

Different collection types

There are three collection types in PL/SQL:
  • nested tables
  • index-by tables
    also known as associative arrays
  • varrays
Nested tables extend the functionality of index-by tables. The main difference is that nested tables can be stored in a table column while index by tables can not.

Summary

  Index-by Nested tables varray
Look up numbers/strings numbers numbers
Max. elements unlimited unlimited fixed
Always dense no no yes
constructors no yes yes
Storable in tables no yes yes
«globally» declarable no yes yes

Use for collections

This example shows how a table can be returned from a pl/sql function.
poor man's text index is an example that uses collections to search in the middle of words in a table. (ie where filed like '%word%')
Index by tables are also used in bulk collect statements.

Collection functions

The collection functions operate on nested tables and varrays.

cardinality

collect

powermultiset

powermultiset_by_cardinality

set