Collections in PL/SQL – Types, Syntax & Examples

Collections in PL/SQL are essential data structures used to handle multiple rows of data within a PL/SQL block, procedure, function, or package. They play a similar role to arrays in other programming languages and help manage and manipulate data efficiently.

In this guide, we’ll explore what collections are, the different types of collections in PL/SQL, their syntax, examples, and use cases. We will also look at multiset operators, performance tips, and best practices.

What Are Collections in PL/SQL?

Collections in PL/SQL are ordered groups of elements, all having the same data type. They allow developers to write more flexible and efficient code by handling multiple elements of data using a single variable. Collections can be used to pass data between procedures and functions, fetch bulk data from cursors, or perform data manipulations in memory.

Types of Collections in PL/SQL

Oracle provides three types of collections:

1. Associative Arrays (Index-By Tables)

Associative arrays are key-value pairs, where the keys (indexes) can be either numbers or strings. These are ideal when you do not know the number of elements beforehand and when performance is critical.

DECLARE
  TYPE EmployeeTable IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
  employees EmployeeTable;
BEGIN
  employees(1) := 'Alice';
  employees(2) := 'Bob';
  DBMS_OUTPUT.PUT_LINE(employees(1));
END;

2. Nested Tables

Nested tables are similar to one-dimensional arrays but can be stored in database columns. They allow dynamic modification (delete, extend) of elements.

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  numbers NumList := NumList(10, 20, 30);
BEGIN
  numbers.DELETE(2);
  DBMS_OUTPUT.PUT_LINE(numbers.COUNT);
END;

3. Varrays (Variable-Size Arrays)

Varrays are ordered collections with a fixed upper limit. They are stored in the database and are ideal when the number of elements is known and remains relatively constant.

DECLARE
  TYPE GradeArray IS VARRAY(3) OF NUMBER;
  grades GradeArray := GradeArray(80, 85, 90);
BEGIN
  FOR i IN 1 .. grades.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(grades(i));
  END LOOP;
END;

Set Operators for Collections in PL/SQL

Oracle provides set operators for working with collections:

Operator	Description
`MULTISET UNION`	Combines two collections including duplicates
`MULTISET UNION DISTINCT`	Combines collections excluding duplicates
`MULTISET EXCEPT`	Returns elements in one collection but not the other
`MULTISET INTERSECT`	Returns elements common to both collections
`SET()`	Removes duplicates from a collection

Example:

DECLARE
  TYPE NumList IS TABLE OF NUMBER;
  a NumList := NumList(1, 2, 3);
  b NumList := NumList(3, 4, 5);
  c NumList;
BEGIN
  c := a MULTISET INTERSECT b;
  FOR i IN 1 .. c.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(c(i));
  END LOOP;
END;

When to Use Each Collection Type

Collection Type	Use When…
Associative Array	The size is unknown and you need key-value access
Nested Table	You need a resizable collection stored in the DB
Varray	You have a small, fixed-size set of values

Best Practices for Collections in PL/SQL

Always initialize collections before use.
Use BULK COLLECT and FORALL to improve performance with large datasets.
Prefer associative arrays for temporary, in-memory data processing.
Use nested tables and varrays when collections need to be stored in the database.
Use multiset operators to simplify complex data manipulation.

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.