Tutorial: Running a Stored Procedure in a Remote Oracle Database

A remote procedure call (RPC) runs a procedure or function at a remote database. An RPC performs any work defined in the remote procedure. To run a remote procedure or function, you can identify the remote procedure or function by appending @dblink to the end of its name. The dblink is a database link to the database that contains the remote procedure or function. You can also create a synonym that points to the remote procedure or function.

Meet the following conditions before performing the sample RPC in this topic:

Satisfy the prerequisites described in "Preparing to Access and Modify Information in Multiple Oracle Databases".
Create a database link from the local database to any remote database that contains a procedure or function that is being called. In the example in this topic, the SYSTEM user at the ii1.example.com database uses a database link that connects to the SYSTEM user at the ii2.example.com database. See "Tutorial: Creating a Database Link" for information about creating such a database link.
Ensure that the hr sample schema is installed on the remote database. The hr sample schema is installed by default with Oracle Database.

This topic uses ii1.example.com and ii2.example.com as sample databases. You can substitute any two databases in your environment that meet these conditions.

For this example, assume the following:

A company keeps its human resources information in the hr schema at the ii2.example.com database.
The hr schema does not exist at the local ii1.example.com database.
When an employee leaves the company, a procedure called add_job_history in the hr schema inserts a row into the hr.job_history table. The row contains information about the history of the employee with the company.
The employee with an employee_id of 127 is leaving the company.
You want to connect to the ii1.example.com database and run the add_job_history procedure at the ii2.example.com database to record the job history for employee 127.

To run an RPC to record the job history of the employee:

On a command line, open SQL*Plus and connect to the ii1.example.com database as the SYSTEM user.

See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.
Run the add_job_history procedure at the ii2.example.com database to record the job history of the employee:
```
exec hr.add_job_history@ii2.example.com(127,'14-JAN-99','26-JUN-06','ST_CLERK',50);
```
Commit the changes:
```
COMMIT;
```
This step is not necessary if the remote procedure commits.

Optionally, query the hr.job_history table at the ii2.example.com database to see the inserted row:

SELECT * FROM hr.job_history@ii2.example.com ORDER BY employee_id;

The output will be similar to the following:

EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
        101 21-SEP-89 27-OCT-93 AC_ACCOUNT           110
        101 28-OCT-93 15-MAR-97 AC_MGR               110
        102 13-JAN-93 24-JUL-98 IT_PROG               60
        114 24-MAR-98 31-DEC-99 ST_CLERK              50
        122 01-JAN-99 31-DEC-99 ST_CLERK              50
        127 14-JAN-99 26-JUN-06 ST_CLERK              50
        176 24-MAR-98 31-DEC-98 SA_REP                80
        176 01-JAN-99 31-DEC-99 SA_MAN                80
        200 17-SEP-87 17-JUN-93 AD_ASST               90
        200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
        201 17-FEB-96 19-DEC-99 MK_REP                20

Notice that the job history of the employee with an employee_id of 127 is recorded in the table.

Tutorial: Running a Stored Procedure in a Remote Oracle Database

Related Topics