ORA-00913: too many values insert wm_concat string into table

Question

When I use wm_concat to concatenate several tuples into one tuple.

For example

ID Items
1 'apple'
2 'peach'
2 'banana'

 declare 
  v_name_l: varchar(100);
  v_name_b: varchar(100);
begin
     select wm_concat(Items) into v_name_l from Item group by ID having count(*)=1;
     select wm_concat(Items) into v_name_b from Item group by ID having count(*)=2;
     execute immediate ('Insert into  apr values('||v_name_l||','||v_name_b||')');
end;

Another table apr structure is
Name1 Name2

name1 and nam2 are varchar(100);

There is a error raise in

*execute immediate execute immediate ('Insert into  apr values('||v_name_l||','||v_name_b||')')"

   ORA-00913: too many values

I think the wm_concat to concatenate mutiple rows in one single string;
How can I fix it?

Answer 1

I think nothing wrong with wm_concat queries. You just need to escape quotes. Because you are referencing your query as string in this case lets say:

v_name_l is 'apple, peach, banana' and v_name_b is 'x, y, z'

So when you define as current query:

Insert into  apr values('||v_name_l||','||v_name_b||')

Oracle translates to:

Insert into apr values(apple, peach, banana, x, y, z)

And database recognize every comma separated value as a column but the table has just two column instead of 6. If you escape quotes:

Insert into  apr values('''||v_name_l||''','''||v_name_b||''');

then oracle will read like:

Insert into apr values('apple, peach, banana', 'x, y, z');

I hope that was the reason:)