Running a stored procedure across db Link

Question

I have a script whose sole propose is just to transfer data from one schema to another via a db link. Part of the process however is that after the data has been loaded, a stored procedure needs to be ran which runs some calculations and updates some additional tables. How can I have my script run a stored procedure across a link? Rough idea of what I'm thinking...

Begin

Insert into schema@myLink
 SELECT * from testSchema
;

commit;

[email protected]();

commit;

dbms_output.put_line("schema has been updated");
END;

Answer 1

You will need to add the table to the insert statement...

Insert into schema.table@myLink SELECT * from testSchema;

and re-arrange the procedure call a bit...

schema.procedure@myLink();

You might also consider removing the first commit so that the insert and procedure call are done in the same transaction.