How to manipulate VARRAYS in sql (oracle)?

Question

Supposing i am using a table person, and persons might have multiple last names, so that attribute should be a varray of 3 elements for example (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 varray(3) of varchar2(10);
CREATE TABLE person
(
    ID NUMBER NOT NULL 
  , last_name lastn
  , CONSTRAINT EXEMPLE_PK PRIMARY KEY 
      (
          ID 
      )
     ENABLE 
);

insert into person values(1,lastn('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):

insert into table
    (select last_name from example where id=1)
   values lastn('new');

This is the case where i want to get persons that have a first last name of 'bilel' and second last_name as 'dani'

select * from person where id in (select id from pernom p,table(p.last_name) 
where column_value(1)='bilel' and column_value(2)='dani');

I know that it doesn't work like that, but i want to know CRUD(create update delete) statements in that case. and select statement with varray in where statement.

Thanks for your response.

Alex Poole · Accepted Answer · 2018-12-04T19:34:00.157

From the docs:

Oracle does not support piecewise updates on VARRAY columns. However, VARRAY columns can be inserted into or updated as an atomic unit.

As shown in the examples there, you can manipulate the collection through PL/SQL instead; incuding adding an element to the array:

declare
  l_last_name lastn;
begin
  select last_name into l_last_name
  from person where id = 1;

  l_last_name.extend();
  l_last_name(l_last_name.count) := 'third';

  update person
  set last_name = l_last_name
  where id = 1;
end;
/

PL/SQL procedure successfully completed.

select last_name from person where id = 1;

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

You can also do this via cast(multiset(...) as ...):

-- rollback; to reverse PL/SQL block actions above

update person p
set last_name = cast(multiset(
    select column_value
    from table (last_name)
    union all
    select 'third' from dual
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

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

That explodes the existing last_name value into multiple rows, union's in a new value, and then converts the combined result back into your varray type.

And you can delete or update elements in a similar way:

update person p
set last_name = cast(multiset(
    select column_value
    from table (last_name)
    where column_value != 'bilel'
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

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

update person p
set last_name = cast(multiset(
    select case column_value when 'third' then 'second' else column_value end
    from table (last_name)
  ) as lastn)
where id = 1;

1 row updated.

select last_name from person where id = 1;

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

Can you show me how to set a where condition in this case ? I want to select last_name from person where last_name=lastn('bilel'); that sql didn't work ! — BHA Bilel, Dec 25 '18 at 19:48

score 0 · Answer 2 · answered Dec 25 '18 at 22:24

For the select statement, i've figured out the solution, which goes like this :

select * from person p where id in (select id from table(p.last_name) where 
column_value='bilel' intersect select id from table(p.last_name) where 
column_value='dani');

or

select * from agent ag where id in (select id from table(ag.prenom) 
t1,table(ag.prenom) t2,table(ag.prenom) t3 where t1.column_value='bilel' and 
t2.column_value='dani' and t3.column_value='third');

How to manipulate VARRAYS in sql (oracle)?

2 Answers2