Deep Dive — COLLECTION METHODS

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:

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

<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

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

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

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

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

-- 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

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 EXTENDed: 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.
• 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 — the structure these operate on.
• FORALL — INDICES OF / VALUES OF for sparse writes.
• BULK COLLECT — always produces a dense 1..n collection.
• NOCOPY — avoiding copies.