#

| Chapter 19 Nested Tables and VARRAYs |

## 19.6 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.

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

19.5 Collection Pseudo-Functions |  | 19.7 Example: PL/SQL-to-Server Integration

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.