We have a new project where we need to copy a large dataset from Oracle (on
prem) to SQL Server (cloud).
I found that just a
insert into table@mssql
select * from table;
Returns ORA-02025: all tables in the SQL statement must be at the remote
database
I am trying out the SQL Server Gateway driver from Oracle because the
documentation looks like it would work with the Remote Insert Rowsource
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/gmswn/database-gateway-for-sqlserver-features.html#GUID-513FBA3C-3458-4129-93E4-38DB2DF97F7A
However I get the same error. Does anyone know if this should work?
We've converted it to a PL/SQL loop inserting row by row but 5000 rows is
taking 8 minutes. Oh and the funny thing is that this SQL Server instance
is running on Oracle Cloud Infrastructure.
Any other ways you can think of to do this quick? 8 minutes is killer.
Thanks,
Jeff