Copy table data using database link in PL/SQL
519457Jul 10 2006 — edited Jul 10 2006HI,
I would like to copy data into a table in a remote database, from the table in my local database. I cannot use the exp/imp facility and has to do this using the PL/SQL procedures.
I have created a dblink to the remote database for this purpose.
What I am trying to do is to create a procedure as shown below.
Declare
--variables
cursor c1
Begin
open cursor c1;
Loop
fetch c1 into variables
exit when c1%Notfound;
Insert into tablename@dblink(col1, col2, col3,...)
values(var1, var2, var3,...) etc.
end loop;
Commit;
End
Is this the right way to do this?
what are the potential pitfalls?
My table has about a 100,00 rows or so. and the table structure is exactly the same in two databases.
Also,if the method can be used, can I also use bulk fetch and do bulk insert over a dblink??
Deepak