Collection Built-Ins

As we've seen already, there are a number of built-in functions and procedures that apply to collection variables. These functions are collectively known as "collection methods" in honor of their object-like invocation syntax. That is, you invoke them using a "variable-dot-method " style. For functions, use this syntax:

result := collection_variable.function_method (method_argument);

where "result" must be of a datatype that is type-compatible with the method. For procedures, the syntax is:

collection_variable.procedure_method (method_arguments);

The following methods are not available from within SQL; they can only be used within PL/SQL programs. The first seven are functions (and are the same methods available for PL/SQL or index-by tables in PL/SQL Release 2.3), and the last three are procedures. For quick reference purposes, this section documents each method using a standard format, which includes an example.

• COUNT function
• Returns the current number of elements in a collection.
• DELETE procedure
• Removes one or more elements from the "middle" of a nested table. Reduces COUNT if the element is not already DELETEd. Does not apply to VARRAYs.
• EXISTS function
• Returns TRUE or FALSE to indicate whether the specified element exists.
• EXTEND procedure
• Increases the number of elements in a collection. Increases COUNT.
• FIRST, LAST functions
• Return the smallest (FIRST) and largest (LAST) subscripts in use.
• LIMIT function
• Returns the maximum number of allowed elements in a VARRAY.
• PRIOR, NEXT functions
• Return the subscript immediately before (PRIOR) or after (NEXT) a specified subscript. Useful for nested tables that might be sparse.
• TRIM procedure
• Removes collection elements at the "end" of the collection. Reduces COUNT if elements are not DELETEd.

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

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.

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.

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.

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.

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.

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.

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.