Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Running procedures remotely
On Mon, 30 Aug 1999 09:22:41 -0400, Kenneth C Stahl
<BluesSax_at_Unforgettable.com> wrote:
>Here is the general idea for something I need to do but am not quite
>sure how I would do it.
>
>I have two servers and both are running Oracle databases. The first
>server has some real-world connectivity for telephony functions which
>use Oracle and the second machine has a database which is used as a
>repository for a variety of other data items.
>
>When certain events occur on the first server I want to be able to run a
>pl/sql stored procedure on the remote machine and pass it about 20
>parameters and then get a result set back which would then be used by
>the telephony functions.
>
>I know how to define a database link, so that isn't my problem, but what
>I don't know is how I would call a procedure that doesn't reside in the
>local database.
>
>Can anyone help?
You reference the remote procedures/functions the same way as you reference remote tables, views, etc.
e.g.:
remote_procedure_at_db_link(<parameters>)
schema.remote_package.package_function_at_db_link(<parameters>)
Of course, you can create synonyms to "hide" the fully qualified names.
On the other hand, you have another alternative: create a local procedure and inside that procedure you reference the remote tables. This way the procedure will execute on the local server.
The decision between remote/local procedure may depend on each server's load and the logic inside the procedure.
>Ken
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)