Collection Datatypes:

Collection datatypes can contain multiple records. They are categorized into two sub category:

• VARRAY (Varying Array)
• NESTED TABLE (Multiple records)

Varray

An array is an ordered set of data elements. All elements of a provided array are of the similar datatype. Every element has an index that is a number corresponding to the element's position in the array.   A number of elements in an array are the size of the array. An Oracle arrays are of variable size that is why they are known as VARRAYs. The maximum size must be specified although creating varrays.

Declaring a Varray does not occupy space. It describes a type that can be used as

• a column of a relational table of datatype.
• An object type attributes.
• The PL/SQL variable, function or parameter return type.

An array object is stored in line, that is, in the similar tablespace as the other data in its row. The given example describes this concept.

Example

CREATE OR REPLACE TYPE price_list AS VARRAY(6) OF number(9) ;

CREATE TABLE prods

(

Pno number, rate price_list

) ;

Inserting Values

Inserting records are always completed only by using Constructor method.

INSERT INTO prods VALUES# insert query#

(1, price_list (12,34,56,78));

INSERT INTO prods VALUES# insert query#

(2, price_list (12,34,56));

Here the maximum values which can be specified are only 5. The number of values in which the column can cotain must be less than or equal to 5. On exceeding this maximum value, the insertion leads to the subsequent error:

INSERTINTO            prods VALUES

(4, price_list (12,3,5,6,71,90));

ERROR at line 2:

ORA-22909: exceeded maximum VARRAY limit