René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Constructors for PL/SQL collection types

Variables whose type is either nested table or varray must be initialized with a constructor before they can be used. On the other hand, there are no constructors needed (and also available) for index-by tables.

Simple form

In its simplest form, a constructor has the same name as its type and does not take any arguments: 
declare
  type table_varchar  is table of varchar2(10);
  type varray_varchar is varying array(20) of varchar2(10);

  var_table_varchar  table_varchar;
  var_varray_varchar varray_varchar;
begin
  var_table_varchar  := table_varchar();
  var_varray_varchar := varray_varchar();
end;
/

Constructor initializing some elements in the collection

The constructor can also be used to initialize some elements within the collection to a value: 
declare
  type table_varchar  is table of varchar2(10);
  type varray_varchar is varying array(20) of number;

  var_table_varchar  table_varchar;
  var_varray_varchar varray_varchar;
begin
  var_table_varchar  := table_varchar('one', 'two', 'three', 'four');
  var_varray_varchar := varray_varchar(1, 2, 3, 4, 5, 6);
end;
/

Omitting the constructor

If the constructor is ommited and an element is being assigned to, an ORA-06531 is thrown: 
declare
  type table_varchar  is table of varchar2(10);

  var_table_varchar  table_varchar;
begin
  var_table_varchar(1) := 'one';
end;
/

ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 6

Index-by types

As already mentioned, index-by collections don't have a constructor; they can just be used: 
declare
  type assoc_varchar  is table of varchar2(10) index by pls_integer;

  var_assoc_varchar  assoc_varchar;
begin
  var_assoc_varchar(5) := 'five';
end;
/