sql - Oracle - How to bulk insert values in one query with an array of values to insert -

i have these tables:

a { id, name }  b { id, name, aid } 

aid foreign key table a

so have list of strings need inserted table b.

list: names['name1', 'name2'] 

also have select statement fetches me list of a.id's.

list: id's[1, 2] 

the problem table b needs have rows inserted every a.id queried , @ same time string list should taken consideration.

end result id's (1, 2) instance, 4 rows created (2 each id because every id needs insert string in list):

insert b (id, name, aid) values (b_id_seq.nextval, name1, 1) insert b (id, name, aid) values (b_id_seq.nextval, name2, 1)  insert b (id, name, aid) values (b_id_seq.nextval, name1, 2) insert b (id, name, aid) values (b_id_seq.nextval, name2, 2) 

what have gotten work write select statement returns array of id's. tried implement select these 2 lists without luck.

first attempt use in clause inside of specific column value part , second attempt use insert all. not figure out how generate insert statements dynamically in last case tough.

how 1 solve kind of insert statement?

this sounds candidate use pl/sql. can dynamic queries this... truth told don't need , can away cursor usage.

create proc myproc(inlistname in customtype)  --this cursor list of ids... cursor1    select id table;  c1row cursor1%rowtype;  begin    --this cursor opens , "outer loop"   open cursor1;   loop     -- retreive 1 row.     fetch cursor1 c1row;     exit when cursor1%notfound;       --not exact syntax here.. idea...       name in inlistnames       loop         insert b (id, name, aid) values (b_id_seq.nextval, name, c1row.id);       end loop;              end loop;   close cursor1; end / 

note: untested , off top of head... work along these lines. if you're doing lot of data, i'd recommend instead bulk insert (just google oracle bulk insert...) , use instead, performance wise works better.

note2: using custom datatype pass in parameter contain list of names. here link show how can set user defined type that.
passing array of data input parameter oracle procedure

alternatively, if that's over-kill or feel it's over-engineered, maybe can somehow run query list of names? if that's case, create second cursor list of names , iterate on that.