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

Declaration of a PL/SQL collection type

The three collection types are declared as follows:

Nested table

declare
  type varchar2_nested_table_type is 
       table of varchar2(100);
begin
  null;
end;
/

create type number_nested_table_type as
       table of number
/
See deleting and inserting from/to a nested table for an example.

Varray

declare
  type varchar2_varray_type is
       varray(50) of varchar2(100);
begin
  null;
end;
/

create type varchar2_varray_type as
       varray(50) of varchar2(100)
/
In the example above, 50 indicates the maximum count of elements in the varray.

Index-by Table

declare
  type varchar2_index_by_type is
       table of varchar2(100)
       index by pls_integer;
begin
  null;
end;
/
Index by tables cannot be declared «globally»; the following construct generates a PLS-00355: use of pl/sql table not allowed in this context. 
create or replace type number_index_by_type is
       table of number
       index by pls_integer;
/

is versus as

Type declarations within the declare section of a block must be created with is, not as. For example, the following block generates a PLS-00103: Encountered the symbol "TABLE" when expecting one of the following: object opaque 
declare
  type varchar2_index_by_type as -- as wrong here, use is instead
       table of varchar2(100)
       index by pls_integer;
begin
  null;
end;
/