I have seen this once before and immediately forgot about it, so I write it down to remember:
My Oracle Data Integrator mappings / scenarios do not run.
ODI creates inexecutable SQL code which generate an ORA-00928 error.
The generated code looks like:
Beware of operator names in ODI that consist of SQL reserved words, like:
So not name a set operator "Union", for example.
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.