Collection Comparison

Composite Data Type

1) Collection : In a collection, the internal components always have the same data type, and are called elements. We can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, we either define a collection type and then create a variable of that type or use %TYPE.

2) Record : In a record, the internal components can have different data types, and are called fields. We can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, we either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

We can create a collection of records, and a record that contains collections.

Varray	NestedTable	Associative Arrays(Index by tables)
1.Fixed Number of elements 2.Subscript will be sequence no 3.Uninitialized Status : Null - Initialization required  normally 4.Extend required 5. Dense or Sparse : Always dense 6. Can be stored in DB as a column 7. Number of Elements:  Specified 8. Where Defined : In PL/SQL block or package or at schema level. 9. Can Be ADT Attribute Data Type: Only if defined at schema level	1.Any number of elements 2.Subscript will be sequence no 3.Null,  Initialization required 4.Extend required 5. Starts dense, can become sparse 6.Can be stored in DB as a column 7. Unspecified 8. In PL/SQL block or package or at schema level. 9. Only if defined at schema level.	1.Any number of elements 2.Substring can be arbitrary number or string 3.Empty, Initialization Not required 4.Extend not required 5. Either 6.Cannot be stored in DB as a column 7. Unspecified 8. In PL/SQL block or package. 9. No

Translating Non-PL/SQL Composite Types to PL/SQL Composite Types

If we have code or business logic that uses another language, we can usually translate the array and set types of that language directly to PL/SQL collection types. For example:

Non-PL/SQL Composite Type	Equivalent PL/SQL Composite Type
Hash table	Associative array
Unordered table	Associative array
Set	Nested table
Bag	Nested table
Array	Varray

I hope you all have enjoyed reading this article. Comments are welcome....

Related Posts:

-Bulk Collect Save Exceptions

-Collection Details