I'm using SSIS to run a few inserts to Oracle 12.1. If I do the following, everything works fine.
MERGE INTO table1 t1 USING( select * from table2 where ...) sON t1.Id = s.Id WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
However, if try either of the following, I get an error.
MERGE INTO table1 t1 USING( select * from ( select * from table3 where ... ) where ...) sON t1.Id = s.Id WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
Or,
MERGE INTO table1 t1 USING( with rawresults as ( select * from table3 where ... ) select * from rawresults, table2 t2 where ...) sON t1.Id = s.Id WHEN NOT MATCHED THEN INSERT (...) VALUES (...)
Both of the latter examples work just fine when executed in SQL Developer. If I execute these in SSIS using Oracle provider for OLE DB, I get ORA-00903 invalid table name. Also, in the case of the middle example, if a create a view for the inline subquery and use that in the MERGE INTO statement, it works. Unfortunately, SSIS error reporting doesn't show which table name or line number is causing the error.
Is this a known restriction in Oracle provider for OLE DB? If so, is it documented somewhere?
I'm using OraOLEDB 12.1.0.1.0.