23.4 Remote Procedure Calls

One of the most important enhancements contained in PL/SQL Version 2.0 and all subsequent releases is the ability to execute remote procedure calls or RPCs. A remote procedure (module) call occurs when you execute a program from within one database that is stored in another database. To execute a remote module, you simply attach a database link after the module name, as follows:

emp_name := full_name@new_york (:emp.employee_id);

The full specification for a remote module call is:

schema_name.[package_name.]module_name@database_link_name

where [package_name.] is the optional package name, necessary if the module is contained in a package.

Just as you should hide the owner of a module with a synonym, you should do the same thing for remote module calls. Programmers should never have to know where a module is located nor who owns it. You should never want them to know because if that information is placed in a program, that program is much more vulnerable to breakage. The very nature of a remote module call indicates that the code can be moved to a different remote database. Never let a programmer hardcode such information in a program.

When you call a remote module and it raises an exception, that exception is passed back as raised to the calling program -- for all but user-defined exceptions. If your remote module raises a NO_DATA_FOUND exception, your exception handler traps that error. If, on the other hand, your module uses RAISE_APPLICATION_ERROR to raise a custom exception (error numbers between -20000 and -20999), then that specific exception is lost in the translation back to the calling program. Instead, error ORA-06510 is always returned to the local program.