Collections in Oracle - Part 3

Collections continued .....

Associative Array: ( PL/SQL Level )
TYPE table_type_name IS TABLE OF datatype [ NOT NULL ]
       INDEX BY index_type;
Nested Table: (DATABASE Level)
CREATE [ OR REPLACE ] TYPE type_name AS | IS
       TABLE OF element_datatype [ NOT NULL ];

Varray : (DataBase Level)
CREATE [ OR REPLACE ] TYPE type_name AS | IS
       VARRAY (max_elements) OF element_datatype [ NOT NULL ];
Note:

If you have created a nested table or VARRAY type in the database, you can use the ALTER TYPE command to change several of the type's characteristics.

(The INVALIDATE and CASCADE options are provided to either invalidate all dependent objects or propagate the change to both the type and any table dependents.)

Example:

CREATE TYPE list_t AS VARRAY(10) OF VARCHAR2(80);

ALTER TYPE list_t MODIFY ELEMENT TYPE VARCHAR2(100) CASCADE;

ALTER TYPE list_t MODIFY LIMIT 100 INVALIDATE;

Nested Table: (PL/SQL Level)

TYPE type_name IS TABLE OF element_datatype [ NOT NULL ];

Varray : (PL/SQL Level)

TYPE type_name IS VARRAY (max_elements)
       OF element_datatype [ NOT NULL ];

DROP TYPE:

DROP TYPE type_name [ FORCE ];

FORCE

Tells Oracle to drop the type even if there is a reference to it in another type. For example, if an object type definition uses a particular collection type, you can still drop the collection type using the FORCE keyword.

We must ensure:
 Nested Tables and Varrays are initialized before we try to use them.(Declaration / Executable block)
 Associative arrays do not need to be initialized before you assign values to them.

Note:

We can copy the entire contents of one collection to another as long as both are built from the exact same collection type (two different collection types based on the same datatype will not work).

When you do so, initialization comes along "for free."

A collection is empty after initialization. No rows are defined within it. A collection is, in this way, very much like a relational table. A row is defined by assigning a value to that row. This assignment can be done through the standard PL/SQL assignment operation, by fetching data from one or more relational tables into a collection, or by performing an aggregate assignment (in essence, copying one collection to another).