# Deep Dive — COLLECTION METHODS

> `COUNT` tells you how many. It does not tell you *where* — for that you need `FIRST`, `NEXT` and `EXISTS`.

Used in: [Chapter 4 — In-Memory Lookup](../course-plan.md) · Appendix entry: [COLLECTION METHODS](../appendix.md#collection-methods) · Related: [ASSOCIATIVE ARRAY](associative-array.md), [FORALL](forall.md), [BULK COLLECT](bulk-collect.md)

---

## 1. What It Is and When

Every collection (associative array, nested table, varray) exposes the same set of methods. What differs is which ones apply and what "empty slot" means:

| Method | Assoc. array | Nested table | Varray | Meaning |
| --- | --- | --- | --- | --- |
| `COUNT` | ✓ | ✓ | ✓ | number of elements that exist |
| `EXISTS(i)` | ✓ | ✓ | ✓ | TRUE if subscript `i` holds an element (never raises) |
| `FIRST` / `LAST` | ✓ | ✓ | ✓ | lowest / highest existing subscript; NULL if empty |
| `NEXT(i)` / `PRIOR(i)` | ✓ | ✓ | ✓ | next / previous existing subscript; NULL at the ends |
| `DELETE` | ✓ | ✓ | ✓ | remove all elements |
| `DELETE(i)` / `DELETE(i, j)` | ✓ | ✓ | ✗ | remove one / a range (leaves holes) |
| `EXTEND` / `EXTEND(n)` / `EXTEND(n, i)` | ✗ | ✓ | ✓ | append null / n nulls / n copies of element i |
| `TRIM` / `TRIM(n)` | ✗ | ✓ | ✓ | remove the last / last n elements |
| `LIMIT` | NULL | NULL | ✓ | maximum size |

The rule that matters: **a collection can be sparse**. After `DELETE(i)` or with keyed inserts, subscripts have gaps, and `1 .. COUNT` is wrong.

## 2. Syntax

```sql
<coll>.COUNT   <coll>.EXISTS(i)   <coll>.FIRST   <coll>.LAST   <coll>.NEXT(i)   <coll>.PRIOR(i)
<coll>.DELETE   <coll>.DELETE(i)   <coll>.DELETE(i, j)
<coll>.EXTEND   <coll>.EXTEND(n)   <coll>.EXTEND(n, i)   <coll>.TRIM   <coll>.TRIM(n)   <coll>.LIMIT
```

All are called with dot notation on the variable. `EXISTS`, `NEXT`, `PRIOR` and the `DELETE(i)` forms take a subscript argument of the collection's index type.

## 3. Examples on the Course Schema

### 3a. Iterating a sparse collection — FIRST / NEXT

```sql
DECLARE
  TYPE t_tier_map IS TABLE OF customers.tier%TYPE INDEX BY PLS_INTEGER;
  l_tier t_tier_map;
  k PLS_INTEGER;
BEGIN
  -- keys are customer ids: 17, 2093, 55012, ...
  FOR r IN (SELECT customer_id, tier FROM customers WHERE tier = 'VIP') LOOP
    l_tier(r.customer_id) := r.tier;
  END LOOP;

  k := l_tier.FIRST;
  WHILE k IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(k || ' => ' || l_tier(k));
    k := l_tier.NEXT(k);
  END LOOP;
END;
```

`FOR i IN 1 .. l_tier.COUNT` here would try subscripts 1, 2, 3 … and raise `NO_DATA_FOUND` on the first gap.

### 3b. EXISTS as the guard

```sql
IF l_tier.EXISTS(r.customer_id) THEN
  v_tier := l_tier(r.customer_id);
ELSE
  v_tier := 'NONE';
END IF;
```

The alternative — `BEGIN v := arr(k); EXCEPTION WHEN NO_DATA_FOUND THEN ... END;` — works, but raising an exception per missing key inside a 500M-row loop is measurably slower than a boolean test.

### 3c. DELETE(i) in memory, then FORALL INDICES OF

```sql
DECLARE
  CURSOR c IS SELECT order_id, amount FROM orders WHERE status = 'NEW';
  TYPE t_rows IS TABLE OF c%ROWTYPE;
  l_rows t_rows;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_rows LIMIT 10000;
    EXIT WHEN l_rows.COUNT = 0;

    FOR i IN 1 .. l_rows.COUNT LOOP                     -- dense right after the fetch: OK
      IF l_rows(i).amount <= 0 THEN
        l_rows.DELETE(i);                               -- now sparse
      END IF;
    END LOOP;

    FORALL i IN INDICES OF l_rows                       -- not 1 .. COUNT
      UPDATE orders SET status = 'PRICED' WHERE order_id = l_rows(i).order_id;
    COMMIT;
  END LOOP;
  CLOSE c;
END;
```

`COUNT` after the deletes is the number of *remaining* elements — smaller than `LAST`. Any `1 .. COUNT` loop after a `DELETE(i)` is a bug.

### 3d. Nested table: initialize, EXTEND, then assign

```sql
DECLARE
  TYPE t_ids IS TABLE OF NUMBER;          -- nested table (no INDEX BY)
  l_ids t_ids;                            -- atomically NULL: not usable yet
BEGIN
  l_ids := t_ids();                       -- constructor: empty, usable
  l_ids.EXTEND(3);                        -- three NULL slots: subscripts 1..3
  l_ids(1) := 1001; l_ids(2) := 1002; l_ids(3) := 1003;

  l_ids.EXTEND;                           -- one more
  l_ids(l_ids.LAST) := 1004;

  l_ids.TRIM;                             -- removes 1004 again; COUNT = 3, LAST = 3
END;
```

Skipping the constructor → `ORA-06531: Reference to uninitialized collection`. Skipping `EXTEND` → `ORA-06533: Subscript beyond count`. Associative arrays need neither.

### 3e. Releasing memory

```sql
-- package-level cache used by a nightly job
CREATE OR REPLACE PACKAGE BODY tier_cache AS
  l_tier t_tier_map;
  PROCEDURE load IS ...;                  -- fills l_tier
  PROCEDURE clear IS
  BEGIN
    l_tier.DELETE;                        -- memory becomes reusable by this session
  END;
END;
```

`DELETE` empties the collection; the PGA it used is available to the session again (it is not necessarily returned to the OS immediately). Without it, a package-level collection holds its memory until the session ends — in a connection pool, that is "never".

### 3f. COUNT vs LAST on sparse data

```sql
DECLARE
  TYPE t_map IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  m t_map;
BEGIN
  m(10) := 1; m(20) := 2; m(30) := 3;
  m.DELETE(20);
  DBMS_OUTPUT.PUT_LINE('COUNT=' || m.COUNT || ' FIRST=' || m.FIRST || ' LAST=' || m.LAST);
  -- COUNT=2 FIRST=10 LAST=30
  DBMS_OUTPUT.PUT_LINE('NEXT(10)=' || m.NEXT(10) || ' EXISTS(20)=' || CASE WHEN m.EXISTS(20) THEN 'Y' ELSE 'N' END);
  -- NEXT(10)=30 EXISTS(20)=N
END;
```

## 4. What Breaks at Scale

- `EXISTS` and keyed access are cheap; `FIRST`/`NEXT` traversal is linear — fine. What is not fine is `COUNT` called inside a hot loop's condition on a nested table that is being `EXTEND`ed: compute it once per batch.
- `DELETE(i)` on a huge nested table does not shrink it; the slot stays allocated until `TRIM` or `DELETE`. For memory, prefer building a new dense collection over punching thousands of holes.
- Copying a collection (`a := b`, passing `IN OUT` without `NOCOPY`) copies every element — see [NOCOPY](nocopy.md).
- Iterating with `FIRST`/`NEXT` while inserting new keys into the same collection — the traversal sees or misses the new keys depending on their position. Do not modify the key set while walking it.

## 5. Pitfalls

1. `1 .. COUNT` on anything that might be sparse — `NO_DATA_FOUND` (assoc. array) or `ORA-22160` (in `FORALL`).
2. `EXTEND` on an associative array — compile error; just assign to the new key.
3. Nested table without constructor — `ORA-06531`.
4. `DELETE(i)` on a varray — not allowed; only `TRIM` from the end.
5. `TRIM` after `DELETE(LAST)` — `TRIM` removes the *slot*, which was already empty, so you lose the element before it. Use one or the other.
6. `NEXT(i)` when `i` itself does not exist — returns the next existing subscript anyway (no error); `PRIOR` likewise. Handy, but easy to misread.
7. Assuming `DELETE` returns memory to the OS — it makes it reusable within the session. If PGA must actually drop, end the session (or the pooled connection's logical session).

## 6. Related

- [ASSOCIATIVE ARRAY](associative-array.md) — the structure these operate on.
- [FORALL](forall.md) — `INDICES OF` / `VALUES OF` for sparse writes.
- [BULK COLLECT](bulk-collect.md) — always produces a dense 1..n collection.
- [NOCOPY](nocopy.md) — avoiding copies.