how to add an element into a nested table in sql (oracle)?

Question

Supposing i am using a table person, and persons might have multiple last names, so that attribute should be stored in a nested table (it's not about where to store last names), here is a simple sql for creating the type last name, the table person and adding an example row in oracle's sql developper (11G XE):

create type lastn as table of varchar2(10);
CREATE TABLE person
(
ID NUMBER NOT NULL 
, last_name lastn
, CONSTRAINT EXEMPLE_PK PRIMARY KEY 
(
 ID 
)
ENABLE 
)nested table last_name store as ln;
insert into person values(1,ln('dani','bilel'));

I know how to update all last names at once, but i need to preserve existing last names and add other last names, or remove a single last name without affecting the others. In a nutshell, i want my code to be like (i am not familiar with PL/SQL):

update person set last_name=last_name+'third last name' where id=1;

I know that it doesn't work like that, should i use PL/SQL ?, isn't it posible other ways ?

Please excuse my question, and thank you for your response.

Answer 1

You can insert into the nested table by using a table collection operator:

insert into table(select last_name from person where id = 1) values ('third');

1 row inserted.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'bilel', 'third')

You can delete elements the same way:

delete from table(select last_name from person where id = 1) where column_value = 'bilel';

1 row deleted.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'third')

and you can even update them:

update table(select last_name from person where id = 1)
set column_value = 'second' where column_value = 'third';

1 row updated.

select last_name from person where id = 1;

LAST_NAME                                         
--------------------------------------------------
LASTN('dani', 'second')