After Exception handling, the topic which we finished off with the last PL/SQL tutorial, collection is the most demanded topic on my social media. That is why our next series of tutorials will be based on PL/SQL collections. Starting with today.

What are PL/SQL Collections in Oracle Database?

A homogeneous single dimension data structure which is made up of elements of same datatype is called collection in Oracle Database. In simple language we can say that, an array in Oracle Database is called Collection.

Definition
A homogeneous single dimension data structure which is made up of elements of same datatype is called collection in Oracle Database.

Why we call collection a homogeneous data structure?

As we know that array consists data of same datatype and so does the PL/SQL collection which is why we call them homogeneous data structure.

The structure of PL/SQL collections consist of a cell with subscript called index. Data is stored into these cells and can be identified and accessed using the index number. This is again very similar to the structure of arrays, but unlike array PL/SQL Collections are strictly one-dimensional.

Info Byte:
The collection in Oracle Database are strictly One-Dimensional. It is not possible to realize them on 2D co-ordinates. However when the collection has an attribute of object type or collection type then it is possible to realize a multi-dimensional array.

Types of PL/SQL Collections in Oracle Database

PL/SQL collections can be divided into two categories:

1. Persistent and
2. Non-persistent.

Persistent collection, as the name suggests, are those which physically store the collection structure with the data into the database and can be accessed again if needed. Whereas non-persistent collection only stores data and structure for one session.

On the basis of above categories collections are further divided into three types:

1. Nested Tables
2. Variable Sized Arrays or VARRAYs and
3. Associative arrays.

Nested Table – Nested tables are persistent collection which means they can be stored into the database and can be reused. Nested tables has no upper limits on rows thus they are unbounded collections. Nested tables are initially dense but can become sparse through deletion.

VARRAYs – Similar to Nested tables Variable-Sized Arrays are also persistent collections thus they can be created in database as well as PL/SQL block and can be reused.  But unlike nested tables VARRAYs are bounded in nature which means that they can hold only a fixed amount of elements.

Info byte:
The size and storage schema of VARRAYs makes them different from nested tables.

Associative Array – Unlike nested table and VARRAYs, associative arrays are non-persistent collections thus they cannot be stored into the database. Since they cannot be store hence they cannot be reused but they are available in PL/SQL block for the session. But similar to nested tables associative arrays are unbounded which means they also don’t have lower and upper limits on rows.

Commonly used terms in PL/SQL Collection.

Bounded & Unbounded Collection – A collection which has lower or upper limits on values of row number or say a collection which can hold only limited number of elements are called bounded collections. A collection which has no lower or upper limits on row numbers are called unbounded collections.

Dense & Sparse Collection. – Collections is said to be dense if all the rows between the first and the last are defined and given a value. And a collection in which rows are not defined and populated sequentially are called sparse collection.

That’s it for this tutorial. You can help others in learning as well as help me and my channel in growing by sharing this blog with your friends or on your social media.

You can now get the updates of our blogs, giveaways along with other interesting things as it happens right in your inbox just subscribe to our newsletter. Thanks & have a great day!