|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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 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.
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.
The collection functions operate on nested tables and varrays.