Exploring PL/SQL Nested Tables in Oracle

In this tutorial, you will learn how to declare and initialize Oracle PL/SQL collections (Nested Tables).

Oracle PL/SQL – Nested tables

Nested tables are very similar to the PL/SQL tables, which are known in Oracle as index-by tables. Nested tables extend the functionality of index-by table by adding extra collection methods (known as table attributes for index-by tables) and by adding the ability to store nested tables within a database table, which is why they are called nested tables.

Nested tables can also be manipulated directly using SQL, and have additional predefined exceptions available.

Other than these extra features, the basic functionality of a nested table is the same as a PL/SQL table. A nested table can be thought as off as a database table with two columns-key and value. Like index-tables, nested tables can be sparse, and the keys do not have to be sequential.

Declaring a Nested Table

The syntax for creating a nested table type is

TYPE table_name is TABLE OF table_type [NOT NULL];

where table_nameis the name of the new type, and table_type is the type of each element in the nested table. Table_type can be a built-in type, a user-defined object type, or an expression using % TYPE.

Note

The only syntactic difference between index-by tables and nested tables is the presence of the INDEX BY BINARY_INTEGER clause. If this clause is not present, then the type is a nested table type. If this clause is present, then the type is an index-table type.

The following declarative section of code shows some valid table declarations:

DECLARE
--Define a table type based on an object type
TYPE t_ClassTab IS TABLE OF Classobj;


--A type based on%ROWTYPE
Type t_StudentsTab IS TABLE Of students%ROWTYPE;

--Variables of the above types
v_ClassList t_ClassesTab;
v_StudentList t_StudentsTab;

Oracle PL/SQL – Nested Table Initialization

When a table is declared as in the preceding block, it is initialized to be atomically NULL, like an object type. If you try to assign to a NULL table, the error “ORA-6531: Reference to uninitialized collection” which corresponds to the predefined exception COLLECTION_IS_NULL, is raised.

Continuing the previous example, the following execution section will raise this error:

BEGIN
--This assignment will raise COLLECTION_IS_NULL because
--v_ClassList is automatically null.
v_ClassList(1) := ClassObj ('HIS', 101, 'History 101', 30, 0, 4, NULL);
END;

So how do you initialize a nested table? This can be done by using the constructor. Like an object type constructor, the constructor for a nested table has the sam ename as the table type itself. However, it takes as an argument a list of elements, each of which should be type compatible with the table element type.

The following example illustrates the use of nsat3eed table constructor:

DECLARE
TYPE t_NUmbersTab IS TABLE OF NUMBER;

-- Create a table with one element.
v_Tab1 t_NumbersTab := t_NumbersTab(-1);

-- Create a table with five elements.
v-Primes t_numbersTab := t_NumbersTab(1, 2, 3 , 5, 7);

-- Craete a table with no elemnts.
v_Tab2 t_NumbersTab := t_NumbersTab();
BEGIN
-- Assign to v_Tab1(1). This will replace the value ready
-- in v_Tab(1), which was initilized to -1.
v_Tab(1) := 12345;
END;

Empty Tables

Note the declaration of v_Tab2 in the preceding block:

-- Create a table with no elements.
v_Tab2 t_NumbersTab := t_NumbersTab();

v_Tab2 is initialized by calling the constructor with no arguments. This creates a table that has no elements but is not atomically NULL. The following block illustrates this:

DECLARE
TYPE t_WordsTab IS TABLE OF VARCHAR2(50);

--Create a NULL table.
v_Tab1 t_WordsTab;

--Create a table with one element, which itself is NULL.
v_Tab2 t_WordsTab := t_WordsTab();
BEGIN
IF v_Tab1 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('v_Tab1 is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('v_Tab is not NULL');
END IF;

IF v_Tab2 IS NULL THEN
DBMS_OUTPUT.PUT_LINE('v_Tab2 is NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('v_Tab2 is not NULL');
END IF;
END;

If we run this block we get the following output:

v_Tab1 is NULL
v_Tab2 is not NULL

Keys At Initialization

When a table is initialized using a constructor, the elements of the table are numbered sequentially, ranging from 1 to the number of elements specified in the constructor call. During later processing, the values stored at some keys may be deleted (Using the DELETE method). when a nested table is selected from the database, the keys are renumbered if necessary to be sequential as they are at initialization.

Previously published at https://www.foxinfotech.in/2019/06/oracle-pl-sql-collections-nested-tables.html