GBase 8s Collection Attributes: count, limit, exist, first, last, next, prior

When you work with collections (index-by tables, nested tables, or varrays) in GBase 8s SPL, a set of built-in attributes lets you inspect element counts, navigate subscripts safely, and check boundaries without custom loops. Here's a practical overview of the seven attributes you'll use most — count, limit, exist, first, last, next, and prior.

Attribute Quick Reference

• count() – returns the number of elements currently in the collection.
• limit() – returns the maximum declared size of a VARRAY (useless for unbounded collections).
• exist(index) – returns TRUE if an element exists at the specified subscript.
• first() – returns the smallest subscript in use.
• last() – returns the largest subscript in use.
• next(index) – returns the next higher subscript in use, or NULL if none.
• prior(index) – returns the next lower subscript in use, or NULL if none.

Subscripts can be integers or strings (VARCHAR2), making these attributes equally useful for numerically indexed and associative arrays.

count – Total Number of Elements

DECLARE
   TYPE sname_tab_type IS TABLE OF students.name%TYPE INDEX BY BINARY_INTEGER;
   sname_tab sname_tab_type;
   i INT := 1;
BEGIN
   FOR student_record IN (SELECT name FROM students WHERE specialty = 'Computer Science') LOOP
       sname_tab(i) := student_record.name;
       i := i + 1;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Total students in Computer Science: ' || sname_tab.COUNT);
END;

limit – Maximum Subscript for VARRAYs

DECLARE
   TYPE sname_varry_type IS VARRAY(15) OF students.name%TYPE;
   sname_varry sname_varry_type := sname_varry_type('Alice', 'Bob', 'Charlie');
BEGIN
   DBMS_OUTPUT.PUT_LINE('Maximum subscript: ' || sname_varry.LIMIT);
   DBMS_OUTPUT.PUT_LINE('Element count: ' || sname_varry.COUNT);
END;

exist – Element Existence Check

DECLARE
   TYPE sname_tab_type IS TABLE OF VARCHAR2(10);
   sname_tab sname_tab_type;
   v_id students.student_id%TYPE;
BEGIN
   v_id := &student_id;
   IF sname_tab.EXIST(1) THEN
       NULL; -- already initialized
   ELSE
       sname_tab := sname_tab_type('Alice', 'Bob', 'Charlie');
   END IF;
   SELECT name INTO sname_tab(1) FROM students WHERE student_id = v_id;
   DBMS_OUTPUT.PUT_LINE('Student name: ' || sname_tab(1));
END;

first and last – Boundary Subscripts

Even when subscripts start at a negative number, first returns the smallest subscript and last the largest.

DECLARE
   TYPE sname_tab_type IS TABLE OF students.name%TYPE INDEX BY BINARY_INTEGER;
   sname_tab sname_tab_type;
   i INT := -10;
BEGIN
   FOR student_record IN (SELECT name FROM students WHERE specialty = 'Computer Science') LOOP
       sname_tab(i) := student_record.name;
       i := i + 10;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('First subscript: ' || sname_tab.FIRST);
   DBMS_OUTPUT.PUT_LINE('Element count: ' || sname_tab.COUNT);
   DBMS_OUTPUT.PUT_LINE('Last subscript: ' || sname_tab.LAST);
END;

next and prior – Safe Iteration Through Sparse Subscripts

Combine first and next to loop through all elements, handling gaps gracefully.

DECLARE
   TYPE sname_tab_type IS TABLE OF students.name%TYPE INDEX BY BINARY_INTEGER;
   sname_tab sname_tab_type;
   i INT := -10;
   counter INT;
BEGIN
   FOR student_record IN (SELECT name FROM students WHERE specialty = 'Computer Science') LOOP
       sname_tab(i) := student_record.name;
       i := i + 10;
   END LOOP;
   counter := sname_tab.FIRST;
   WHILE counter <= sname_tab.LAST LOOP
       DBMS_OUTPUT.PUT_LINE('sname_tab(' || counter || ') = ' || sname_tab(counter));
       counter := sname_tab.NEXT(counter);
   END LOOP;
END;

Mastering these seven attributes makes collection handling in GBase 8s much more readable and efficient. They're the building blocks for any SPL routine that iterates over dynamic lists, validates subscripts, or extracts boundaries in a gbase database. If you're writing procedural logic on GBASE's engine, keep this cheat sheet close.