Skip to main content
← PL/SQL Topics

Collections

Explain navigating a sparse collection with FIRST, LAST, NEXT and PRIOR

Navigating a Sparse Collection

ExplanationOracle Fusion / EBS · Technical · PL/SQLHigh confidenceGenerated

After deletes leave gaps, iterate with FIRST/NEXT (or LAST/PRIOR) instead of a numeric FOR loop.

How it works

A numeric FOR i IN c.FIRST .. c.LAST loop raises NO_DATA_FOUND on a missing subscript. FIRST and LAST give the lowest and highest defined indexes; NEXT(i) and PRIOR(i) give the next/previous defined index or NULL at the end. A WHILE loop over these safely visits only populated elements — the only correct way to walk an associative array with string keys or any collection after DELETE(i).

How it works
DECLARE
  TYPE t IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
  c t;
  i PLS_INTEGER;
BEGIN
  c(1) := 'a'; c(5) := 'b'; c(9) := 'c';
  c.DELETE(5);                          -- now sparse: 1 and 9
  i := c.FIRST;
  WHILE i IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(i || ' => ' || c(i));
    i := c.NEXT(i);
  END LOOP;
END;
/

Related questions

GroundingGenerated

Model-generated, grounded against the curated knowledge layer. Check specifics against your instance.

Have a follow-up, or a different question?

Continue in Ask Oracle AI