options
Racer I., February 23, 2018 - 9:41 am UTC
Hi,
For the error handling requirement you can look at
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ For working in chunks you could try BULK INSERT into the local (Temp) table and then INSERT AS SELECT through the DB-Link. I.e. like Conner said, but in smaller chunks. Could use commits (empties the temp table if so configured) or deleting the temp table if you can't afford intermediate commits.
Yet another options is calling a helper package on the remote site to do the processing, which could fetch from a (reverse) db-link and do regular FORALL inserts.
regards,
reverse db-link
Racer I., February 23, 2018 - 9:58 am UTC
Hi,
Maybe I can put a related question here?
When Oracle executes a statement spanning both sides of a db-link on the remote site you will see new statements there using "@!" as a reverse db-link. Can your own statements use that too or only Oracle itself? I.e. in the above remote procedure scenario? Often there are now official reverse links available.
It would habe to be dynamic (using EXECUTE IMMEDIATE) I guess. Google will not search for "@!"...
regards,
Sceptic
Gh, February 25, 2018 - 8:00 am UTC
In your second solution regarding plsql you mentioned
15 -- use GTT 16 insert into t@np12 17 select * from tmp;
Well you build tmp from dba_objects in same db.
And insert target is remote.
Didn't catch the thing.
insert into t@np12 select * from dba_objects straight ;why using gtt?
February 25, 2018 - 8:01 am UTC
You cannot bulk bind/forall insert a plsql table type across a database link. Hence a workaround is to load the plsql data into GTT, and then use that to copy to the remote database.
This worked well.
A reader, March 14, 2019 - 9:14 pm UTC
I wrote a script that refreshes a website by merging changes from the source table into the remote website table. It didn't work because merge doesn't work for bulk collect. I changed it to write to a temp table then merge from the temp to the remote as described in this article.
A test merging into a table on the same server as the source took over two minutes to run (60k records).
Using the temp table technique and merging to the remote ran in 2 seconds!
Wow!
March 18, 2019 - 9:55 am UTC
glad we could help