Oracle Nested Tables.

Preamble

In Oracle PL/SQL Nested Tables is a column type that stores an unlimited row set in a certain order.

When you extract a nested table value from a database into a Nested Tables variable, PL/SQL produces rows of consecutive indexes starting with 1. Using these indexes, you can refer to individual rows of the Nested Tables variable.

Syntax to define and then declare a variable of type Nested Tables in Oracle PL/SQL

TYPE nt_type IS TABLE OF element_type [NOT NULL];
var_nt nt_type;

Parameters and arguments of the column type

• nt_type is the type name used later to declare collections.
• element_type – any PL/SQL data type, except: REF CURSOR
• var_nt is a variable of Nested Tables type.

Note:

• An uninitialized nested table variable is the zero collection.
• You must initialize it, either by emptying it or by assigning it a value other than NULL.

An example of Nested Tables as a local type

Let’s consider an example of Oracle PL/SQL which defines a local nested table type Roster, declares a variable of this type names (initializes it with the constructor) and defines the print_names procedure which prints Nested Tables. (The procedure uses FIRST and LAST collection methods.)

The example calls the procedure three times: after initializing a variable, after changing the value of one element and after using the constructor to change the value of all elements. After calling the second constructor, the nested table has only two elements. The reference to element 3 will cause error ORA-06533.

DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type

-- is a nested table variable initialized by the constructor:

names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

PROCEDURE print_names (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);

FOR i IN names.FIRST . names.LAST LOOP -- For the first element
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;

DBMS_OUTPUT.PUT_LINE('---');
END;

BEGIN
print_names('Initial Values:');

names(3) := 'P Perez'; -- Change the value of one element
print_names('Current Values:');

names := Roster('A Jansen', 'B Gupta'); -- Change the whole table
print_names('Current Values:');
END;

As a result, we get:
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
Perez
R Singh
---
Current Values:
A Jansen
B Gupta

The following example defines an offline stored nested table type, nt_type and an offline stored procedure for printing a variable of this print_nt type. (The procedure uses FIRST and LAST collection methods.)

An anonymous block declares a variable of nt_type, initializes it empty with the constructor and calls print_nt twice: after initializing the variable and after using the constructor to change values of all elements.

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;

CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
i NUMBER;
BEGIN
i := nt.FIRST;

IF i IS NULL THEN
DBMS_OUTPUT.PUT_LINE('nt is empty');
ELSE
WHILE i IS NOT NULL LOOP
DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
i := nt.NEXT(i);
END LOOP;
END IF;

DBMS_OUTPUT.PUT_LINE('---');
END print_nt;

DECLARE
nt nt_type := nt_type(); -- nested table variable initialized empty
BEGIN
print_nt(nt);
nt := nt_type(90, 9, 29, 58);
print_nt(nt);
END;
As a result:

nt is empty
---
nt.(1) = 90
nt.(2) = 9
nt.(3) = 29
nt.(4) = 58
---

Using Nested Tables

The nested table is suitable if:

• The number of elements is not specified.
• Index values are not consecutive.
• You must remove or update some elements, but not all elements at once.

The Nested Tables shall be stored in a separate stored table, the database table generated by the System. When you access Nested Tables, the Nested Tables database connects to its stored table. This makes Nested Tables suitable for queries and updates that affect only certain items in the collection.

You must create a separate search table with multiple entries for each row of the main table and access it through join queries.

PL/SQL tutorial: How To Create Nested Table collection in PL/SQL Block

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.