Oracle7 Server Distributed Systems Volume I: Distributed Data

Handling Errors in Remote Procedures

When a procedure is executed locally or at a remote location, four types of exceptions can occur:

PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION.

PL/SQL predefined exceptions, such as NO_DATA_FOUND keyword.

SQL errors, such as ORA-00900 and ORA-02015.

Application exceptions, which are generated using the RAISE_APPLICATION_ERROR() procedure.

When using local procedures, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

EXCEPTION
   WHEN ZERO_DIVIDE THEN
   /* ...handle the exception */

Notice that the WHEN clause requires an exception name. If the exception that is raised does not have a name, such as those generated with RAISE_APPLICATION_ERROR, one can be assigned using PRAGMA_EXCEPTION_INIT, as shown in the following example:

...
null_salary EXCEPTION;
PRAGMA EXCEPTION_INIT(null_salary, -20101);
...
RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
WHEN null_salary THEN
...

When calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return ORA-06510 to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.