execute immediate sql concatenated insert string

Question

If I run an execute immediate on this I have no problems:

execute immediate 'insert into tab_aantal2 (table_name,count_1,value_1) select table_name,count_1,value_1 from(select ''CUSTOMERS'' table_name,count(1) count_1,CUSTOMERID||''~''||NAME||''~''||ADDRESS
value_1  from PJO.CUSTOMERS group by CUSTOMERID||''~''||NAME||''~''||ADDRESS having count(1) > 1)';

This string is stored in a table and looks like:

'insert into tab_aantal2 (table_name,count_1,value_1) 
select table_name,count_1,value_1 from(select ''CUSTOMERS'' table_name,count(1) count_1,CUSTOMERID||''~''||NAME||''~''||ADDRESS  value_1 
from PJO.CUSTOMERS 
group by CUSTOMERID||''~''||NAME||''~''||ADDRESS  
having count(1) > 1)'

If I put the string in a variable the DBMS_OUTPUT.PUT_LINE returns this exact string but when I try to execute the variable I get 'an invalid SQL statement' so I reckon it must be something with the quotes but I just cant figure it out. I ultimately will try to loop through several hundreds of tables (running the above on different tables).

Answer 1

The string which is actually passed to the EXECUTE IMMEDIATE shouldn't have the apostrophes doubled - that's only needed when the PL/SQL compiler is parsing the string. Change the data in your database to:

insert into tab_aantal2 (table_name,count_1,value_1) select table_name,count_1,value_1 from(select 'CUSTOMERS' table_name,count(1) count_1,CUSTOMERID||'~'||NAME||'~'||ADDRESS value_1 from PJO.CUSTOMERS group by CUSTOMERID||'~'||NAME||'~'||ADDRESS having count(1) > 1)

or in other words, change all the doubled apostrophes to single apostrophes.