Nested tables are one method you can use in Oracle to store a one-to-many relationship. You essentially put a table inside of another table. The nested table is actually stored separately and is linked to the original table by the unique row id. Working with this kind of table and the data within is different.

First, create a table type that will store the values desired in the nested table.

create or replace type type_letters as table of varchar2(1);

Next, create the new table. For the nested table, give it a field name as normal, then provide the new type as the data type. Finally, the nested table clause will have you provide a name for that actual table mentioned earlier.

create table tbl_test 
 (test_id integer,
 test_name varchar2(20),
 letters type_letters)
 nested table letters store as tbl_type_letters
 /

To insert into the new table, reference the original type as the data type being inserted.

insert into tbl_test values (1, 'First', type_letters ('A','B'));

select * from tbl_test t, table(letters) l;

We can query only for the column_value from letters, as well.

select t.test_id, t.test_name, l.column_value from tbl_test t, table(letters) l;

You can still perform outer queries, as well, using Oracle’s notation (+).

insert into tbl_test (test_id, test_name) values (2, 'Second');

select * from tbl_test t, table(letters) (+) l;