-
Problem of cursors
Hi Friends
I want to insert/update data from one table to another table which is on different server.
There are two server having one database on each.
One databse is clientdb another is Jcastdb.
I have made a database link from clientdb to jcastdb say jcastdb.
I have written a Procedure to update data from clientdb to jcastdb.But when I am executing it, getting error Invalid cursor.
I am new to Pl/Sql can you help me to solve it.Code of procedure is below
*****************************
create or replace procedure updatejcastdb is
no1 number;
no2 number;
var1 varchar2(70);
var2 varchar2(70);
cursor c1 is select * from tblcompany;
cursor c2 is select * from tblcompany@jcastdb;
begin
open c2;
open c1;
loop
fetch c2 into no2,var2;
exit when c2% notfound;
loop
fetch c1 into no1,var1;
exit when c1% notfound;
if no2=no1 then
update tblcompany@jcastdb
set companyname= var1
where companyid=no2;
end if;
end loop;
close c1;
commit;
end loop;
end updatejcastdb;
regards
Praveen
-
Qualify your tablename with schema name as follows:
cursor c2 is select * from schemaName.tblcompany@jcastdb;
-
Why do u write PL/SQL to transfer data.
Use Import/Export utility or use COPY command or use any other tool like informatica
Regards
Abhay.
-
Can you try to use the implicit for loop?
Instead of open c1;loop;fetch c1 into...;close c1;end loop;,
you can use for i in c1;loop...end loop;