I have seen this once before and immediately forgot about it since it was so ridiculous, so I write it down to remember:
My Oracle Data Integrator mappings / scenarios do not run.
ODI creates inexecutable SQL code which generates an ORA-00928 error.
The generated code looks like:
Beware of operator names in ODI that consist of SQL reserved words, like:
Do not name a set operator “Union”, for example. ODI uses the operator name as an alias in SQL without checking for reserved words or any form of escaping.
And once you rename an operator in ODI’s logical view, also rename it manually in the physical view, too, since this is not done automatically.