If you plan to reuse the nested table that you want to create then doing so as a database object is the best choice for you. You can store them in your database permanently and use them whenever you want.

Apart from creating Nested Table type PL/SQL Collection inside a PL/SQL block you can also create them as database object and store permanently. Also you can reuse them whenever you want. Nested table created as database object can be based on either Primitive Datatype or User-Define Datatype. In this tutorial we will concentrate on former and leave the latter for the next tutorial.

How to Create Nested table type collection based on primitive datatype

By primitive datatype we mean the datatypes which are predefined by the language and are named by a reserved keyword. You can refer to this Oracle Document to read more about PL/SQL Datatypes.

The following tables have no constraint, index or anything designed on them and are created purely for demonstrating how to create nested table as database object.

Step 1: Set Server output on

SET SERVEROUTPUT ON;

Step 2: Create Nested Table type collection

CREATE OR REPLACE TYPE my_nested_table IS TABLE OF VARCHAR2 (10);
/

The above statement on successful execution will create a nested table with name ‘my_nested_table’ which will be based on primitive datatype VARCHAR2.

Step 3: How to use nested table?

The collection type which we created above can be used to specify the type of a column of a table.

CREATE TABLE my_subject(
	  sub_id    	NUMBER,
	  sub_name  	VARCHAR2 (20),
	  sub_schedule_day    my_nested_table
) NESTED TABLE sub_schedule_day STORE AS nested_tab_space;
/

The above table is a normal table except that its 3^rd column is of nested table type which can hold multiple values. In order to define a column of a table as nested table type you have to tell the compiler the name of the column and a storage table. You can do so by using NESTED ABLE and STORE AS clause, as we did here in line number 5. Using clause NESTED TABLE we specify the name of the column and using STORE AS clause we specify the storage table for the nested table.

You can refer to the video tutorial where I have step wise explained the above table creation process.

Insert rows into the table

INSERT INTO my_subject (sub_id, sub_name, sub_schedule_day)
VALUES (101, 'Maths', my_nested_table('mon', 'Fri'));

You insert rows into the nested table same as you insert into the normal table. However in order to insert data into the column of nested table type you first have to write the name of nested table which in our case is ‘my_nested_table’ (refer step 2) and then write the data according to the datatype of your nested table and enclose it inside the parenthesis.

Retrieve data from the table

A simple SELECT DML statement can be used to retrieve the data from the table.

SELECT * FROM my_subject;

This simple DML statement will show you all the data stored into the table that we created above. To see the data from a specific row you can use WHERE clause with SELECT DML

SELECT * FROM my_subject WHERE sub_id = 101;

If you want then you can take help of sub-query to just check the data from the column which you defined as nested table type.

SELECT * FROM TABLE (
  SELECT sub_schedule_day FROM my_subject WHERE sub_id = 101
);

The above query will show you the data of subject which has subject-id 101 only from sub_schedule_day column. In this query we used TABLE expression to open the instance and display the data in relational format.

Update data of the table

You can either update all the values of the column which you define as nested table or you can update a single instance of the same.

Update all the values of the nested table type column.

UPDATE my_subject SET sub_schedule_day = my_nested_table('Tue', 'Sat') 
WHERE sub_id = 101;
/

The above query will update all the values of sub_schedule_day from ‘Mon’, ‘Fri’ to ‘Tue’ and ‘Sat’. Now suppose you want to update only a single instance of this column by replacing ‘Sat’ with ‘Thu’. How will you do that?

Update single instance of nested table

In order to update a single instance of nested table type column you can once again take the help of TABLE expression.

UPDATE TABLE
  (SELECT sub_schedule_day FROM my_subject 
  WHERE sub_id = 101) A
SET A.COLUMN_VALUE   = 'Thur' 
WHERE A.COLUMN_VALUE = 'Sat';

The above query will update the value from ‘Sat’ to ‘Thur’ in the table.

Hope you enjoyed reading and learnt something new. Do make sure to subscribe to our channel as many such interesting tutorials are on their way. Thanks & have a great day!