How to declare collections?

Nested tables

TYPE type_name IS TABLE OF element_type [NOT NULL];

With nested tables declared within PL/SQL, element_type can be any PL/SQL datatype except: REF CURSOR

Nested tables declared in SQL (CREATE TYPE) have additional restrictions. They cannot use the following element types:

BINARY_INTEGER, PLS_INTEGER

BOOLEAN

LONG, LONG RAW

NATURAL, NATURALN

POSITIVE, POSITIVEN

REF CURSOR

SIGNTYPE

STRING

PL/SQL Declare

   TYPE TYP_NT_NUM IS TABLE OF NUMBER;

SQL

   CREATE [OR REPLACE] TYPE TYP_NT_NUM IS TABLE OF NUMBER;

Varrays

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) OF element_type [NOT NULL];

size_limit is a positive integer literal representing the maximum number of elements in the array.

PL/SQL Declare

   TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;

SQL

   CREATE [OR REPLACE] TYPE TYP_V_CHAR IS VARRAY(10) OF VARCHAR2(20) ;

Index-by tables

TYPE type_name IS TABLE OF element_type [NOT NULL]

   INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

INDEX BY key_type;

The key_type can be numeric, either BINARY_INTEGER or PLS_INTEGER(9i).

It can also be VARCHAR2 or one of its subtypes VARCHAR, STRING, or LONG. You must specify the length of a VARCHAR2-based key, except for LONG which is equivalent to declaring a key type of VARCHAR2(32760).

The types RAW, LONG RAW, ROWID, CHAR, and CHARACTER are not allowed as keys for an associative array.

Declare

   TYPE TYP_TAB_VAR IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;