oracle, adding a new line to a nested table

Question

I have these three object

create or replace 
type type_client 
( num int , 
  username varchar(30), 
  balance int, 
  ta table_achat, 
  ref_admin ref type_admin,
  member function get_prix_achat_total return int );



create or replace 
type table_achat as table of achat ;


create or replace 
type achat as object ( num_item int , qte int
);

create table table_client OF type_client ;

suppose in an entry of table_client .. we have a nested table like this :

(num_item,qte) : (1 , 5),(2 , 3)

what I want is the nested table be like this (for example):

(num_item,qte) : (1 , 5),(2 , 3)(3 , 44)

What I mean is, how to add a new line to an already created nested table while keeping existing entries? ..

Answer 1

We can use the MULTISET UNION operator to create a new set from two sets. In your case one of those sets is your existing set and the second set is the set of new entries.

Here is a demo based on a simplified version of your set-up:

declare
  nt table_achat;
begin
  nt := table_achat(achat(1 , 5),achat(2 , 3));
  dbms_output.put_line(nt.count());

  nt := nt multiset union table_achat(achat(3 , 44));
  dbms_output.put_line(nt.count());
end;
/

---

Given a table T42 with a column COL_NT which is a nested table of your table_achat type you could insert a new entry in the nested table like this:

insert into the 
(select col_nt from t42 where id = 1) 
values (achat(3,44));