Collection and Record In Oracle

A collection is an ordered group of elements, all of the same type. 

In a collection, the internal components are always of the same data type, and are called elements. We can access each element by its unique subscript. e.g. Lists and arrays.

A record is a group of elements, which can be of different types. 

In a record, the internal components can be of different data types, and are called fields. We can access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL has 3 collection types as below:

• Index-by tables, also known as associative arrays,  are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
•  Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. We can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although we can change the number of elements at runtime). They use sequential numbers as subscripts. We can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created
Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block
Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level
Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical