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 type is creating a nested table table_type [ ]; TYPE table_name is TABLE OF 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 based on an object t_ClassTab IS TABLE OF Classobj; --A based on%ROWTYPE t_StudentsTab IS TABLE Of students%ROWTYPE; --Variables of the above types v_ClassList t_ClassesTab; v_StudentList t_StudentsTab; type type TYPE type Type 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 “ ” which corresponds to the predefined exception COLLECTION_IS_NULL, is raised. ORA-6531: Reference to uninitialized collection Continuing the previous example, the following execution section will raise this error: v_ClassList( ) := ClassObj ( , , , , , , ); ; BEGIN --This assignment will raise COLLECTION_IS_NULL because --v_ClassList is automatically null. 1 '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 , each of which should be type compatible with the table element type. list of elements The following example illustrates the use of nsat3eed table constructor: DECLARE TYPE t_NUmbersTab IS TABLE OF NUMBER; -- Create a table one element. v_Tab1 t_NumbersTab := t_NumbersTab( ); -- Create a table five elements. v-Primes t_numbersTab := t_NumbersTab( , , , , ); -- Craete a table no elemnts. v_Tab2 t_NumbersTab := t_NumbersTab(); BEGIN -- Assign to v_Tab1( ). This will replace the value ready -- v_Tab( ), which was initilized to v_Tab( ) := ; END; with -1 with 1 2 3 5 7 with 1 in 1 -1. 1 12345 Empty Tables Note the declaration of v_Tab2 in the preceding block: -- Create a table no elements. v_Tab2 t_NumbersTab := t_NumbersTab(); with 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 t_WordsTab TABLE VARCHAR2( ); v_Tab1 t_WordsTab; v_Tab2 t_WordsTab := t_WordsTab(); v_Tab1 DBMS_OUTPUT.PUT_LINE( '); DBMS_OUTPUT.PUT_LINE( '); ; v_Tab2 DBMS_OUTPUT.PUT_LINE( '); DBMS_OUTPUT.PUT_LINE( '); ; ; TYPE IS OF 50 --Create a NULL table. --Create a table with one element, which itself is NULL. BEGIN IF IS NULL THEN 'v_Tab1 is NULL ELSE 'v_Tab is not NULL END IF IF IS NULL THEN 'v_Tab2 is NULL ELSE '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