Oracle Database Collections

Types of Collections:

1. PL/SQL Tables or Index By Table or Associative Array
2. VARRAY
3. Nested Tables

Associative Array:

This can only exist in PL/SQL and cannot be created in SQL.

TYPE associative_array_type IS TABLE OF element_type INDEX BY key_data_type

key_data_type - PLS_INTEGER or BINARY_INTEGER or VARCHAR2(size)

v_associative_array associative_array_type;

v_associative_array(index) := value;

Key points to keep in mind before and while using Associative Arrays:

• Unbounded - No upper boundary on the number of entries that can be stored
• They are sparse - index values not necessary to be in sequence.  So there can be gaps between index values
• Cannot be persisted
• No initialization required
• Single dimensional

VARRAY:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE varray_type IS VARRAY(size) OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE varray_type AS VARRAY(size) OF element_type;

v_varray varray_type;

--Initialization is required

v_varray := varray_type();

--Once initialized, make room for adding element(s)

v_varray.extend(n);

--Now, assign

v_varray(index) := value;

Key points to keep in mind before and while using VARRAY:

• Bounded - Upper boundary is required - you should know the size of the collection at the time of declaration
• They are dense - index values are in sequence.  So no gaps between index values
• Can be persisted
• Initialization required
• Single dimensional

Nested Table:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE nested_table_type IS TABLE OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE nested_table_type AS TABLE OF element_type;

v_nested_table nested_table_type;

--Initialization is required

v_nested_table := nested_table_type();

--Once initialized, make room for adding element(s)

v_nested_table.extend(n);

--Now, assign

v_nested_table(index) := value;

Key points to keep in mind before and while using Nested Table:

• Unbounded
• They are dense to begin with, but can become sparse with deletion of elements
• Can be persisted
• Initialization required
• Single dimensional
• Elements are not stored in any particular order