Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.