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 type (not an associative array type). value is a valid value for the elements of the collection_type collection. If collection_type is a varray type, the number of values cannot exceed the maximum value specified for the varray. If collection_type is a nested table type, there is no maximum number of values.

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

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

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

The preceding example calls the constructor twice: first to initialize the varray variable team to NULL, 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)

The result is as follows:

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, then an ty_rec associative array type is defined, where index is of the PLS_INTEGER type and element is of the rec_type type. v_rec is an ty_rec associative array variable. The constructor with => is used to specify the index and its corresponding element. The associative array is then enumerated to output all its elements. The constructor can also be used without specifying the index (i.e., without =>), but this is only applicable to associative arrays where index is of the PLS_INTEGER type. In such cases, the index starts from 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)

The result is as follows:

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

In this example, an associative array type ty1 is declared, where index is of the PLS_INTEGER type and element is of the int type. Then, a variable x of type ty1 is declared. The element values are directly assigned to x within the ty1 constructor. Finally, the index and element of x are iterated and output.