19.6.1 COUNT

Specification

FUNCTION COUNT RETURN BINARY_INTEGER;

Example

FOR element IN 1..my_list.COUNT
LOOP
   DBMS_OUTPUT.PUT_LINE (my_list(element));
END LOOP;
/* Note: If my_list is a nested table with any deleted elements
|| in the middle, the my_list(element) reference above will
|| generate a NO_DATA_FOUND exception.
*/

Returns

Current number of elements in a collection. If elements have been DELETEd or TRIMmed from the collection, they are not included in COUNT.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to an initialized collection with no elements, returns zero. Also returns zero if applied to empty index-by table.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. (This exception is not possible for index-by tables, which do not require initialization.)

19.6.2 DELETE [ ( i [ , j ] ) ]

Specification (overloaded)

PROCEDURE DELETE;
PROCEDURE DELETE (i BINARY_INTEGER);
PROCEDURE DELETE (i BINARY_INTEGER, j BINARY_INTEGER);

Example

CREATE PROCEDURE keep_last (the_list IN OUT List_t)
AS
   first_elt BINARY_INTEGER := the_list.FIRST;
   next_to_last_elt BINARY_INTEGER := the_list.PRIOR(the_list.LAST);
BEGIN
   the_list.DELETE(first_elt, next_to_last_elt);
END;

Action

DELETE without arguments removes all the elements of a collection. DELETE(i) removes the ith element from the nested table or index-by table. DELETE(i,j) removes all elements in an inclusive range beginning with i and ending with j. When you use parameters, DELETE actually keeps a placeholder for the "removed" element, and you can later reassign a value to that element.

WARNING: Confusing behavior results if you TRIM and DELETE the same collection.

Applies to

Nested tables, index-by tables. Also, DELETE without arguments can be applied to VARRAYs.

Boundary considerations

If i and/or j refer to nonexistent elements, DELETE will attempt to "do the right thing" and will not raise an exception. For example, if you have three elements in a TABLE item and DELETE(-5,1), the first element will be deleted. However, DELETE(-5) will do nothing.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.3 EXISTS(i)

Specification

FUNCTION EXISTS (i IN BINARY_INTEGER) RETURN BOOLEAN;

Example

DECLARE
   my_list Color_tab_t := Color_tab_t();
   element INTEGER := 1;
BEGIN
   ...
   IF my_list.EXISTS(element)
   THEN
     my_list(element) := NULL;
   END IF;
END;

Returns

Boolean TRUE if i th element exists, FALSE otherwise. Never returns NULL. If you have used TRIM or DELETE to remove an element i that existed previously, EXISTS(i) returns false.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to an uninitialized (atomically null) nested table or VARRAY, or to an initialized collection with no elements, simply returns FALSE. You can use EXISTS beyond the COUNT without raising an exception.

Exceptions possible

If i is not an integer and cannot be converted to an integer, EXISTS will raise VALUE_ERROR. This exception is possible for any collection method which accepts an argument.

19.6.4 EXTEND [ (n [,i] ) ]

Specification (overloaded)

PROCEDURE EXTEND (n BINARY_INTEGER:=1);
PROCEDURE EXTEND (n BINARY_INTEGER, i BINARY_INTEGER);

Example

CREATE PROCEDURE push (the_list IN OUT List_t, new_value IN VARCHAR2)
AS
BEGIN
   the_list.EXTEND;
   the_list(the_list.LAST) := new_value;
END;

Action

Appends element(s) to a collection. EXTEND with no arguments appends a single null element. EXTEND(n) appends n null elements. EXTEND(n,i) appends n elements and sets each to the same value as the ith element; this form of EXTEND is required for collections with NOT NULL elements.

Applies to

Nested tables, VARRAYs. Applying it to an index-by table will cause a compile-time error.

Boundary considerations

If you have deleted or trimmed from the end of a collection, EXTEND will "jump over" (skip) the deleted elements when it assigns a new index. If n is null, EXTEND will do nothing.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception. An attempt to EXTEND a VARRAY beyond its declared limit raises SUBSCRIPT_BEYOND_LIMIT. If the ith element does not exist, EXTEND will raise SUBSCRIPT_BEYOND_COUNT or, in the case of a VARRAY with a limit less than i, EXTEND will raise SUBSCRIPT_BEYOND_LIMIT.

19.6.5 FIRST, LAST

Specification

FUNCTION FIRST RETURN BINARY_INTEGER;


FUNCTION LAST RETURN BINARY_INTEGER;

Example

IF my_list.EXISTS(my_list.FIRST)
THEN
   my_list(my_list.FIRST) := 42;
ELSE
   my_list.EXTEND;
   my_list(my_list.FIRST) := 42;
END IF;

Returns

FIRST returns the lowest index in use in the collection; LAST returns the highest.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

FIRST and LAST return NULL when applied to initialized collections which have no elements. For VARRAYs which have at least one element, FIRST is always 1, and LAST is always equal to COUNT.

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.6 LIMIT

Specification

FUNCTION LIMIT RETURN BINARY_INTEGER;

Example

IF my_list.LAST < my_list.LIMIT
THEN
   my_list.EXTEND;
END IF;

Returns

The maximum number of elements that is possible for a given VARRAY.

Applies to

VARRAYs only. Returns NULL if applied to nested tables or index-by tables.

Boundary considerations

None

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.7 PRIOR(i), NEXT(i)

Specification

FUNCTION PRIOR (i BINARY_INTEGER) RETURN BINARY_INTEGER;
FUNCTION NEXT (i BINARY_INTEGER) RETURN BINARY_INTEGER;

Example

This function returns the sum of elements in a List_t collection of numbers:

CREATE FUNCTION compute_sum (the_list IN List_t) RETURN NUMBER
AS
   elt BINARY_INTEGER := the_list.FIRST;
   total NUMBER := 0;
BEGIN
   LOOP
      EXIT WHEN elt IS NULL;
      total := total + the_list(elt);
      elt := the_list.NEXT(elt);
   END LOOP;
   RETURN total;
END;

Returns

PRIOR returns the next lower index in use relative to i; NEXT returns the next higher.

Applies to

Nested tables, index-by tables, VARRAYs.

Boundary considerations

If applied to initialized collections which have no elements, returns NULL. If i is greater than or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns NULL. (Currently, if the collection has elements, and i is greater than COUNT, PRIOR returns LAST; if i is less than FIRST, NEXT returns FIRST; however, do not rely on this behavior in future Oracle versions.)

Exceptions possible

If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.

19.6.8 TRIM [ (n ) ]

Specification

PROCEDURE TRIM (n BINARY_INTEGER:=1);

Example

CREATE FUNCTION pop (the_list IN OUT List_t) RETURN VARCHAR2
AS
   l_value VARCHAR2(30);
BEGIN
   IF the_list.COUNT >= 1
   THEN
      /* Save the value of the last element in the collection
      || so it can be returned
      */
      l_value := the_list(the_list.LAST);
      the_list.TRIM;
   END IF;
   RETURN l_value;
END;

Action

Removes n elements from the end of a collection. Without arguments, TRIM removes exactly one element. Confusing behavior occurs if you combine DELETE and TRIM actions on a collection; for example, if an element that you are trimming has previously been DELETEd, TRIM "repeats" the deletion but counts this as part of n, meaning that you may be TRIMming fewer actual elements than you think.

Applies to

Nested tables, VARRAYs. Attempting to TRIM an index-by table will produce a compile-time error.

Boundary considerations

If n is null, TRIM will do nothing.

Exceptions possible

Will raise SUBSCRIPT_BEYOND_COUNT if you attempt to TRIM more elements than actually exist. If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL predefined exception.