Running a stored procedure across db Link

oraclestored-procedures

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;

schema@myLink.DoMagicCalulcations();

commit;

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

Best Answer

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.

Related Question