Creating a schema-level nested table

CREATE OR REPLACE TYPE parent_type IS TABLE OF varchar2(20); 

create or replace type child_type is table of varchar2(20); 

Using nested table as a column type in a database table

create table family (surname varchar2(20),parents parent_type,children child_type)  
nested table parents  
store as parents_tb  
nested table children  
store as children_tb

Accessing Nested Table using Table function in a SELECT statement

declare  
parents_var parent_type := parent_type('Mercy','Prabu');  
child_var child_type := child_type('Ryan','Reza');  
begin  
for rec in (select COLUMN_VALUE member_name from table(parents_var)  
union select column_value member_name from table(child_var) order by member_name desc)   
loop  
	DBMS_OUTPUT.PUT_LINE(rec.member_name);  
end loop;  
end;

Inserting into table with nested tables

declare   
parents parent_type:=parent_type('Mercy','Prabu');  
children child_type:=child_type('Ryan','Reza');  
sname varchar2(3):='Bai';  
begin  
insert into family values(sname,parents,children);  
end; 

select * from family

Updating nested table columns

update table (select children from family where surname='Bai') 
set column_value = 'Jeffrey Ryan' 
where column_value = 'Ryan'

Displaying nested table using select statement

select column_value children from table(select children from family where surname='Bai')