Collection constructors

A collection constructor is a system-defined function with the same name as the collection type. It returns a collection of the specified type.

The syntax for calling a constructor is as follows:

collection_type ( [ value [, value ]... ] )

In this syntax, collection_type is the name of a previously declared nested table or varray (not an associative array) type, and value is a valid value for the elements of the collection_type collection. If collection_type is a varray, the number of values cannot exceed the maximum value specified for the varray. If collection_type is a nested table, there is no maximum value.

If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection containing the specified values.

You can assign the returned collection to a collection variable of the same type in a variable declaration or in the executable part of a block.

Example: Initialize a collection variable to an empty value.

obclient> DECLARE
  TYPE players IS VARRAY(5) OF VARCHAR2(20);
  team PLAYERS := players();   -- Initialize to an empty value

  PROCEDURE print_team (heading VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(heading);
    IF team.COUNT = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Empty');
    ELSE
      FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
      END LOOP;
    END IF;
    DBMS_OUTPUT.PUT_LINE('---');
  END;

BEGIN
  print_team('Team Members:');
  team := players('Charles', 'Carl', 'James', 'Gary','Brian');
  print_team('Team Members:');
END;
/
Query OK, 0 rows affected

Team Members:
Empty
---
Team Members:
1.Charles
2.Carl
3.James
4.Gary
5.Brian
---

In this example, the constructor is called twice: first to initialize the varray variable team to an empty value, and then in the executable part of the block to assign it a new value. The print_team stored procedure displays the initial and final values of team. The print_team procedure uses the collection method COUNT to determine whether team is empty.

Example: Specify the index and element of an associative array by using a constructor.

obclient> declare
    ->     type rec_type is record (
    ->         id number,
    ->         name varchar2(50),
    ->         salary number
    ->     );
    ->     type ty_rec is table of rec_type index by PLS_INTEGER;
    ->     v_rec ty_rec;
    ->     idx PLS_INTEGER;
    -> begin
    ->     v_rec := ty_rec(1 => rec_type(1, 'Alice', 5000),2 => rec_type(2, 'Bob', 6000),3 => rec_type(3, 'Charlie', 7000));
    ->     idx := v_rec.FIRST();
    ->     while idx is not null loop
    ->         dbms_output.put_line('ID: ' || v_rec(idx).id || ', Name: ' || v_rec(idx).name || ', Salary: ' || v_rec(idx).salary);
    ->         idx := v_rec.NEXT(idx);
    ->     end loop;
    -> end;
    -> /
Query OK, 0 rows affected (0.146 sec)

ID: 1, Name: Alice, Salary: 5000
ID: 2, Name: Bob, Salary: 6000
ID: 3, Name: Charlie, Salary: 7000

In this example, a record type is first defined, and then an associative array type ty_rec is defined, where the index is of the PLS_INTEGER type and the element is of the rec_type type. A variable v_rec of the ty_rec type is then declared. The index and corresponding element are specified by using a constructor with the => operator. The elements of the associative array are then enumerated and output one by one.

An associative array also supports a constructor that does not specify the index (that is, a constructor without the => operator), but only for associative arrays where the index is of the PLS_INTEGER type. In this case, the index starts at 1 and increments by 1 by default. Here is an example:

obclient> declare
    ->     type ty1 is table of int index by PLS_INTEGER;
    ->     x ty1;
    ->     idx PLS_INTEGER;
    -> begin
    ->     x := ty1(10, 20, 30);
    ->     idx := x.FIRST();
    ->     while idx is not null loop
    ->       dbms_output.put_line('index: ' || idx || ', Element: ' || x(idx));
    ->       idx := x.NEXT(idx);
    ->     end loop;
    -> end;
    -> /
Query OK, 0 rows affected (0.122 sec)

index: 1, Element: 10
index: 2, Element: 20
index: 3, Element: 30

In this example, an associative array type ty1 is first declared, where the index is of the PLS_INTEGER type and the element is of the int type. A variable x of the ty1 type is then declared. The element value is directly assigned to x in the ty1 constructor. The index and element of x are then traversed and output one by one.