Hello Chris,

Asking a dblink question under this may be irrelevant, but need your suggestion in choosing one of the method for moving the data from One DB to Another DB through DBlink

Source db: Need to join two tables to get the result set that needs to transfer

option1: create a view at source db and querying that view from remote DB as below from remote

declare
cursor c is select * from view@dblink;
begin
loop
fetch c into collection limit 10k;
forall
insert into table;
end loop;
end;
option2: create a GTT in Source DB with result set and performing remote insert from same DB as below
declare
begin
insert into GTT (result set of joining the tables);
insert into remote table@dblink
select * from GTT;
end;

option3: writing a procedure with collection as out parameter and calling that procedure from remote DB
and use that collection for loading data in remote table.

can you help me in knowing pros and cons of above options and your suggestion from the options or out of the options ;)?
thanks in advance