Invoking Remote Subprograms

Remote subprograms (standalone and packaged) can be invoked from within a subprogram, OCI application, or precompiler by specifying the remote subprogram name, a database link, and the parameters for the remote subprogram.

For example, this SQL*Plus statement invokes the procedure fire_emp1, which is located in the database and referenced by the local database link named boston_server:

EXECUTE fire_emp1@boston_server(1043);

You must specify values for all remote subprogram parameters, even if there are defaults. You cannot access remote package variables and constants.

Caution:

Remote subprogram invocations use run-time binding. The user account to which you connect depends on the database link. (Stored subprograms use compile-time binding.)
If a local subprogram invokes a remote subprogram, and a timestamp mismatch is found during execution of the local subprogram, then the remote subprogram is not run, and the local subprogram is invalidated.

Topics:

Synonyms for Remote Subprograms
Committing Transactions

Synonyms for Remote Subprograms

You can create a synonym for a remote subprogram name and database link, and then use the synonym to invoke the subprogram. For example:

CREATE SYNONYM synonym1 for fire_emp1@boston_server;

EXECUTE synonym1(1043);
/

The synonym enables you to invoke the remote subprogram from an Oracle Database tool application, such as a SQL*Forms application, as well from within a subprogram, OCI application, or precompiler.

Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the object and regardless of which database holds the object. However, synonyms are not a substitute for privileges on database objects. Appropriate privileges must be granted to a user before the user can use the synonym.

Because subprograms defined within a package are not individual objects (the package is the object), synonyms cannot be created for individual subprograms within a package.

If you do not want to use a synonym, you can create a local subprogram to invoke the remote subprogram. For example:

CREATE OR REPLACE PROCEDURE local_procedure
  (arg IN NUMBER)
AS
BEGIN
  fire_emp1@boston_server(arg);
END;
/
DECLARE
  arg NUMBER;
BEGIN
  local_procedure(arg);
END;
/

Committing Transactions

All invocations to remotely stored subprograms are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote subprogram is read-only). Furthermore, if a transaction that includes a remote subprogram invocation is rolled back, then the work done by the remote subprogram is also rolled back.

A subprogram invoked remotely can usually execute a COMMIT, ROLLBACK, or SAVEPOINT statement, the same as a local subprogram. However, there are some differences in action:

If the transaction was originated by a database that is not an Oracle database, as might be the case in XA applications, these operations are not allowed in the remote subprogram.
After doing one of these operations, the remote subprogram cannot start any distributed transactions of its own.
If the remote subprogram does not commit or roll back its work, the commit is done implicitly when the database link is closed. In the meantime, further invocations to the remote subprogram are not allowed because it is still considered to be performing a transaction.

A distributed transaction modifies data on two or more databases. A distributed transaction is possible using a subprogram that includes two or more remote updates that access data on different databases. Statements in the construct are sent to the remote databases, and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, then a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating subprograms that perform distributed updates.