Lesson 2	PL/SQL table
Objective	Describe the structure of a PL/SQL table in Oracle 23ai

PL/SQL Table Structure in Oracle 23ai: Associative Arrays

In legacy Oracle material, the phrase PL/SQL table usually means an associative array (formerly also called an index-by table). An associative array is an in-memory set of key–value pairs: you provide an index (the key), and PL/SQL returns the associated element (the value).

In this lesson, you will learn the structure of an associative array—how it is declared, how its indexes work, how to populate it safely, and how to use the most important collection methods (EXISTS, COUNT, FIRST/LAST, NEXT/PRIOR, and DELETE).

1) What an Associative Array Looks Like

An associative array has two parts:

Element type: the datatype of each stored value (scalar, record, or object).
Index type: the datatype of each key (most commonly PLS_INTEGER or VARCHAR2).

Indexes are stored in sort order, not insertion order. If your associative array is string-indexed, session NLS settings (such as NLS_SORT and NLS_COMP) can affect ordering and the behavior of FIRST/LAST/NEXT/PRIOR. :contentReference[oaicite:2]{index=2}

Associative array syntax: TYPE ... IS TABLE OF ... INDEX BY ... — **Associative array type definition syntax (transcribed)**
`-- Associative Array (legacy name: PL/SQL table / index-by table) TYPE type_name IS TABLE OF { scalar_type | variable%TYPE | table.column%TYPE } [NOT NULL] INDEX BY { PLS_INTEGER | VARCHAR2(size) };`
**Key ideas**

`type_name` defines a reusable collection *type*.

The element type can be a scalar (e.g., `NUMBER`), a `%TYPE`, or a column `%TYPE`.

`INDEX BY` defines the *key* datatype; for most server-side PL/SQL, `PLS_INTEGER` is the common choice.

Associative arrays are empty (but not null) until you populate them. :contentReference[oaicite:3]{index=3}

2) Declaring and Populating an Associative Array

Declaration is typically a two-step pattern:

Define a type with TYPE ... IS TABLE OF ... INDEX BY ...
Declare a variable of that type and assign elements using collection(index) := value

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10)
    INDEX BY PLS_INTEGER;

  p_name_tab PetNameTabTyp;
BEGIN
  p_name_tab(1) := 'DOG';
  p_name_tab(5) := 'CAT';  -- sparse index example
END;
/

Example: an associative array can be sparse (indexes need not be contiguous).

3) Dense vs. Sparse: Safe Access and Safe Iteration

Associative arrays are often sparse. That means this pattern can fail if you assume contiguous indexes:

-- Avoid for sparse associative arrays:
FOR i IN 1 .. p_name_tab.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(p_name_tab(i));
END LOOP;

Instead, iterate using FIRST and NEXT, which are designed to handle sparse indexes safely:

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  t   PetNameTabTyp;
  idx PLS_INTEGER;
BEGIN
  t(1) := 'DOG';
  t(5) := 'CAT';

  idx := t.FIRST;
  WHILE idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('t(' || idx || ')=' || t(idx));
    idx := t.NEXT(idx);
  END LOOP;
END;
/

4) Core Collection Methods for Associative Arrays

A collection method is a built-in PL/SQL function/procedure invoked with dot notation:

collection_name.method_name [(parameters)]

In this lesson we focus on the methods you use constantly with associative arrays: EXISTS, COUNT, FIRST, LAST, NEXT, PRIOR, and DELETE.

Important correction: EXTEND and TRIM are for nested tables and varrays, not associative arrays. :contentReference[oaicite:4]{index=4}

Method	Purpose	Notes for associative arrays
`EXISTS(n)`	Test whether element `n` exists	Use before reading `t(n)` to avoid subscript errors
`COUNT`	Return number of populated elements	For sparse arrays, `COUNT` is not the highest index value
`FIRST` / `LAST`	Return lowest/highest existing index	Return `NULL` when empty; return index datatype (number or string) :contentReference[oaicite:5]{index=5}
`NEXT(n)` / `PRIOR(n)`	Return next/previous existing index	Preferred for safe sparse iteration
`DELETE`	Remove elements	`DELETE` (all) and `DELETE(i)` (one) are the common forms :contentReference[oaicite:6]{index=6}

Method Examples

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  t PetNameTabTyp;
  pet_name VARCHAR2(32);
BEGIN
  t(1) := 'DOG';
  t(5) := 'CAT';

  IF t.EXISTS(5) THEN
    pet_name := t(5);
  END IF;
END;
/

EXISTS(n) returns TRUE only when the element is present.

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  t PetNameTabTyp;
  pet_count PLS_INTEGER;
BEGIN
  t(1) := 'DOG';
  t(10) := 'CAT';   -- sparse
  pet_count := t.COUNT;  -- returns 2, not 10
END;
/

COUNT returns the number of populated elements (not the max index).

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  t PetNameTabTyp;
  first_idx PLS_INTEGER;
  last_idx  PLS_INTEGER;
BEGIN
  t(3) := 'FISH';
  t(9) := 'BIRD';

  first_idx := t.FIRST;  -- 3
  last_idx  := t.LAST;   -- 9

  -- Safe sparse iteration:
  DECLARE
    idx PLS_INTEGER := t.FIRST;
  BEGIN
    WHILE idx IS NOT NULL LOOP
      DBMS_OUTPUT.PUT_LINE(t(idx));
      idx := t.NEXT(idx);
    END LOOP;
  END;
END;
/

FIRST/LAST/NEXT support safe iteration for sparse associative arrays.

DECLARE
  TYPE PetNameTabTyp IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  t PetNameTabTyp;
  last_idx PLS_INTEGER;
BEGIN
  t(1) := 'DOG';
  t(5) := 'CAT';

  -- delete one element
  t.DELETE(1);

  -- delete the "end" element explicitly
  last_idx := t.LAST;
  IF last_idx IS NOT NULL THEN
    t.DELETE(last_idx);
  END IF;

  -- delete all
  t.DELETE;
END;
/

DELETE removes one element (by index) or all elements. :contentReference[oaicite:7]{index=7}

5) Neighboring Topic: When EXTEND/TRIM Actually Apply

EXTEND and TRIM apply to nested tables and varrays. You cannot use them with associative arrays. :contentReference[oaicite:8]{index=8}

DECLARE
  TYPE nt_num_t IS TABLE OF NUMBER;  -- nested table (not associative array)
  nt nt_num_t := nt_num_t(10, 20, 30);
BEGIN
  nt.EXTEND;  -- adds one NULL element
  nt.TRIM;    -- removes one element from end
END;
/

6) Oracle 23ai Best Practices: %TYPE, BULK COLLECT, and FORALL

Associative arrays are especially valuable for high-performance PL/SQL because they are in-memory and pair naturally with BULK COLLECT and FORALL to reduce context switching. :contentReference[oaicite:9]{index=9}

Two practical patterns you should recognize:

Use %TYPE / column %TYPE to keep element datatypes aligned with schema changes.
Use BULK COLLECT + FORALL for set-at-a-time operations rather than row-by-row loops.

DECLARE
  TYPE emp_id_tab_t IS TABLE OF employees.employee_id%TYPE
    INDEX BY PLS_INTEGER;

  l_ids emp_id_tab_t;
BEGIN
  -- BULK COLLECT fills sequential indexes (1..COUNT) for PLS_INTEGER-indexed arrays
  SELECT employee_id
  BULK COLLECT INTO l_ids
  FROM employees
  WHERE department_id = 10;

  -- Example: use l_ids in a FORALL DML operation (pattern)
  -- FORALL i IN 1 .. l_ids.COUNT
  --   UPDATE employees SET salary = salary * 1.05
  --   WHERE employee_id = l_ids(i);

END;
/

7) What’s Next

In the next lesson, you will learn how to create and populate a PL/SQL record and how records pair naturally with associative arrays (for example, an associative array of %ROWTYPE records for fast in-memory processing).