PL/SQL Collections

A collection is an ordered group of elements, all of the same type — PL/SQL's answer to an array or a list. There are three kinds, and choosing the right one comes down to two questions: does it need to be stored in a database column, and does it need a fixed maximum size?

Kind	Indexed by	Size	Stored in a column?
Associative array	`PLS_INTEGER` or `VARCHAR2`, any values, need not be contiguous	Unbounded	No — PL/SQL memory only
Nested table	Sequential integers starting at 1	Unbounded	Yes
VARRAY	Sequential integers starting at 1	Fixed maximum, set at declaration	Yes

Associative Arrays

Also called an "index-by table". It behaves like a hash map: any key maps directly to a value, with no need to EXTEND or pre-size it. It exists only for the life of the PL/SQL block or session — it cannot be a column type.

Example:

declare
  type t_scores is table of number index by pls_integer;
  scores t_scores;
  idx pls_integer;
begin
  scores(1)   := 90;
  scores(100) := 60; -- keys need not be contiguous

  idx := scores.first;
  while idx is not null loop
    dbms_output.put_line('scores(' || idx || ') = ' || scores(idx));
    idx := scores.next(idx);
  end loop;
end;
/

Nested Tables

An unbounded, ordered collection that can be a column type or a table type on its own. It starts uninitialized (NULL) until you call its constructor, and grows one slot at a time with EXTEND.

Example:

declare
  type t_names is table of varchar2(20);
  names t_names := t_names(); -- constructor call, now it is an empty table
begin
  names.extend(2);
  names(1) := 'Ana';
  names(2) := 'Bob';

  for i in 1 .. names.count loop
    dbms_output.put_line('names(' || i || ') = ' || names(i));
  end loop;
end;
/

VARRAYs

A "variable-size array" — same idea as a nested table, but with a maximum capacity fixed at declaration. Use it when the upper bound is a known, small, business-meaningful number (a calendar's 12 months, a podium's top 3).

Example:

declare
  type t_top3 is varray(3) of varchar2(20);
  top3 t_top3 := t_top3();
begin
  top3.extend(3);
  top3(1) := 'Gold';
  top3(2) := 'Silver';
  top3(3) := 'Bronze';
  -- top3.extend(1) here would raise ORA-06532: subscript outside limit

  for i in 1 .. top3.count loop
    dbms_output.put_line('top3(' || i || ') = ' || top3(i));
  end loop;
end;
/

Collection Methods

Method	What it does
`.COUNT`	Number of elements currently in the collection.
`.FIRST` / `.LAST`	Lowest / highest index in use.
`.NEXT(n)` / `.PRIOR(n)`	Next / previous index after/before `n` — essential for sparse associative arrays.
`.EXISTS(n)`	True if index `n` has a value.
`.EXTEND(n)`	Grow a nested table or VARRAY by `n` slots (default 1).
`.DELETE(n)`	Remove the element at index `n` (or every element, with no argument).

See it in a full demo: Demo 14 — Collections.