ORA-00933 in ODI procedure

I’m mapping two table in ODI and I have a problem.

i’ve mapped the source table to the target table (called DM_BUSINESS with the columns BUSINESS_ID, NAME, ADDRESS). After that I’ve created a procedure with:

UPDATE dm_business SET name = CONCAT(name, CONCAT(' ', address)) WHERE name IN (SELECT name FROM dm_business GROUP BY name HAVING COUNT (business_id)>1);

When I run this query myself, with SQLDeveloper, I have no problem and it all works fine: it adds the address of the business to its name, when there are more than one business with the same name. When I run the procedure with this task, it gives me error ORA-00933: SQL command not properly ended. I have choosen “Oracle” as target technology. What do I do wrong?

Can you help me? Thank you very much.

Answer

You should remove the semicolon to run it as an SQL statement or wrap your UPDATE with BEGIN..END to run it as a PL/SQL block:

BEGIN
   UPDATE ... ;
END;