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
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;
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.