PL SQL Collections – Types, Features, Syntax, & Examples

According to Enlyft’s research, there are over 75,000 companies taking advantage of PL/SQL for database management. A widely used component of this software is ‘collections’. They enable efficient organization and manipulation of data in Oracle databases. In this blog, we will explore the various types of PL SQL collections, their unique features, syntax, examples, and methods used to modify different attributes of collections.

What is a Collection?

A collection is an organized set of elements belonging to a particular data type. It can be composed of one or more simple data types, such as integer or boolean values. But it can also contain complex records consisting of user-defined structures and objects. 

Each element in the collection has its unique index known as a “subscript”. This subscript allows individual items within a collection to be referenced by name to manipulate them when needed. They are manipulated as a whole by using the BULK option in Oracle databases. 

PL/SQL Collections are extremely effective when dealing with large amounts of similar information. This is because they allow relevant pieces from vast repositories to be separated based on their respective indices. To learn more about the collection, consider taking an online Pl/SQL course.

Data can be referenced in a collection using a combination of the Collection name, Subscript, and Field/Column name, similar to the following: 

"<collection_name>(<subscript>).<column_name>"

Types of PL SQL Collections

PL SQL collections are categorized into three categories according to their structure, subscripts, and storage: 

• Index-by-tables (also known as Associative Arrays) 
• Nested table
• Varrays

Let us learn more about collections in Oracle PL SQL with examples.

Index-by Tables in PL SQL

Index-by tables are a type of associative array used in the programming language PL/SQL developed by Oracle Corporation. It is an indexed table stored inside a database that lets users access data quickly and efficiently using index values as keys, instead of searching through every row or column to find it. Index-by tables can be indexed on one field (using single dimension) or multiple fields (using multi-dimensional indexing). The most common way to use them is for passing parameters into functions as well as returning recordsets from queries.

Features of Index-by tables

Here are some of the features of index-by tables:

• Subscripts in collections can be either integer or string values and should be specified at the time of creation. 
• They are not stored sequentially, are always sparse, and with no fixed size. 
• These types of special collections cannot be stored in database columns but need to be initialized within a subprogram before use.
• Negative subscripts may also come into play giving greater flexibility than other collection methods when maintaining them.
• These collections prove to be most useful for storing small groups with data that will only need to exist during its program/subprogram.
• It does not require initialization before use.
• Finally, since explicit subscripting needs to take place it means BULK COLLECT will have no relevance here.
• An unspecified quantity of elements can be contained by it, and their positions do not need to be known for them to be accessed.
• It is initially empty, but not “null” until you add elements to it.

Syntax and Example of Index-by-Table

The following is the syntax of the index-by table.

TYPE <type_name> IS TABLE OF <DATA_TYPE> INDEX BY VARCHAR2 (10);

In the above syntax, a collection ‘type_name’ of type ‘DATA_TYPE’ is declared as an index-by table. It may either be a simple or complex data type, while its subscript/index variable has been specified to have a VARCHAR2 datatype with a maximum size of 10 characters. Here are some of the  collections in Oracle PL SQL with examples for index-by table:

DECLARE
  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  city_population population_type;
  state_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  city_population('New York') := 5000000; -- Creates new entry
  city_population('Los Angeles') := 3000000;   -- Creates new entry
  -- Looks up the value associated with a string
  howmany := city_population('New York');
  state_population('Texas') := 20000000;
  state_population('Alaska') := 100000; -- Creates new entry
  state_population('Alaska') := 100001; -- Replaces previous value 
  -- Returns 'Alaska' as it comes first alphabetically.
  which := state_population.FIRST;
  -- Returns 'Texas' as it comes last alphabetically.
  which := state_population.LAST;
  -- Returns the value corresponding to the last key, in this
  -- case the population of Texas.
  howmany := state_population(state_population.LAST);
END;

Nested Tables in PL SQL

Nested tables are a special type of PL SQL collection, similar to an array. It is stored as a single row in the database table and can contain zero or more elements as required by your program logic. Nested Tables differ from arrays in that each element has no associated index; therefore they do not need to be explicitly initialized before use nor must all their elements have the same data type as regular collections such as list, vector,  etc.

Features of Nested Tables

Here are some of the features of nested tables:

• Nested tables don’t have an upper size limit, so the collection must be extended with each use of it using the keyword “EXTEND”. 
• Elements are populated sequentially beginning at subscript ‘1’. 
• The type can either be dense or sparse; deletion of individual array elements is possible to make them sparse collections.  
• It gets stored as a system-generated database table which may then be used for running select queries to retrieve values from it when necessary. 
• Subscripting and sequences are not stable. Meaning that both Array element counts and subscripts could vary.
• Before using them in programs, collections must be initialized. 
• Attempting to use any operation (other than EXISTS) on an uninitialized collection will result in an error message being generated. 
• Collections can either be created as database objects that are visible throughout the entire database, or within individual subprograms such that they are only usable there.

Syntax and Example of Nested Table

The following is the syntax used in Oracle PL SQL collections to declare a nested table type:

TYPE <tvpe name> IS TABLE OF <DATA TYPE>;

It defines an array data structure that can store multiple individual elements of the same data type (specified after ‘OF’). The name given at the start (<type name>) will be assigned as this new user-defined datatype for use later on throughout your program.

DECLARE
   TYPE nested_table_type IS TABLE OF VARCHAR2(100);
   my_table nested_table_type;
BEGIN
   -- Populate the nested table
   my_table := nested_table_type('Apple', 'Banana', 'Orange');

   -- Iterate over the elements in the nested table
   FOR i IN 1..my_table.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Element ' || i || ': ' || my_table(i));
   END LOOP;
END;
/

We create a nested table type, named “nested_table_type”, that contains elements of the VARCHAR2(100) data type. We then declare a variable called my_table which is based on this nested table type and add three values: ‘Apple’, ‘Banana’, and ‘Orange’. To complete our example we loop through each element in myTable using DBMS_OUTPUT.PUT_LINE to print them out as output.

The output will be

Element 1: Apple
Element 2: Banana
Element 3: Orange

Varray in PL SQL

Varray (variable-size array) is an Oracle data type that stores a fixed number of elements that have the same data type. It allows us to store collections or groups of similar data items such as department numbers, employee names, etc. in one single structure instead of multiple fields/columns in a table. This helps save space and improves performance by reducing joins when dealing with related information across multiple tables.

Features of Varray

Here are some of the features of Varrays:

1. The upper limit size for a Varray is fixed upon creation and populated with elements in sequential order starting at the subscript ‘1’.
2. Varrays are always dense; individual array elements cannot be deleted, only entire collections or trimmed from the end can be removed entirely.
3. This level of inflexibility renders them most effective when an exact array size has been determined beforehand as it maintains its sequence and number stability throughout any operation (except EXISTS). 
4. All Varrays must first be initialized before use within programs; attempting any operation on uninitialized ones will throw errors during runtime execution 
5. Finally, they may either exist in databases universally or reside solely inside specific subprograms for localized application purposes alone.

Syntax and Example of Varray

The following syntax is used to create a variable-sized array in Oracle:

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

In the above syntax, <type_name> specifies what name is given to the newly created VARRAY object and can then be referenced when using it; <SIZE> sets how many elements will fit inside one instance of that VARRAY; and finally  <DATA_TYPE>, determines which datatype each element in your new Varray should have – such as varchars or integers, etc.

Now look at the following example below. It defines a custom VARRAY type for storing phone numbers. It initializes an instance of Varray type with three phone numbers and iterates through them using a FOR loop, printing each phone number with its index. If there is an exception, it will print the error message.

DECLARE
   TYPE phone_array IS VARRAY(3) OF VARCHAR2(15); -- Creating Varray type. Three elements of a maximum length of 15 characters each can fit in one instance of this array.
   lstPhoneNo phone_array; -- Declaring a variable with the newly created "phone_array" datatype
BEGIN
   lstPhoneNo := phone_array('123-456-7890', '987-654-3210', '000 000 0000'); -- Declare and assign values to our new Varray, using a comma-separated list

   FOR i IN 1..lstPhoneNo.COUNT LOOP -- Loop through all elements of the array
      DBMS_OUTPUT.PUT_LINE('Phone no: ' || LPAD(i, 2, '0') || ' is: ' || lstPhoneNo(i));
   END;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error Occurred! Contact System Admin.');
      RAISE;
END;

The output of the code will be:

Phone no: 01 is: 123-456-7890
Phone no: 02 is: 987-654-3210
Phone no: 03 is: 000 000 0000

PL SQL Collections Methods

PL SQL collections offer built-in collection methods to make working with collections easier. The table below provides an overview of all the available methods and what each one is used for.

Function	Description
EXISTS(n)	Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT	Returns the number of elements that a collection currently contains.
LIMIT	Checks the maximum size of a collection.
FIRST	Returns the first (smallest) index numbers in a collection that uses integer subscripts.
LAST	Returns last (largest) index numbers in collections using integers as subscripts.
PRIOR(n)	Returns the index number before n for a given COLLECTION OR Array.
NEXT(n)	Returns the index next after N.
EXTEND	Appends one null element to a collection.
EXTEND(n)	Appends N null elements to a collection.
TRIM	Removes an element from the end of a collection.

Conclusion

PL SQL collections are powerful tools for working with data in Oracle databases. Each type has its own distinct features and syntax: index-by tables use keys to quickly access data; nested tables resemble arrays, while Varrays store a set amount of elements all the same type. Constructors and initialization methods can be used to first create collections, and then built-in collection operations offer easy ways of manipulating them. Therefore, PL/SQL provides efficient solutions when managing database information.