Handling Errors in Remote Procedures - Oracle 10g

When the database executes a procedure 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 the NO _DATA _FOUND keyword
  • SQL errors such as ORA-00900 and ORA-02015
  • Application exceptions generated using the RAISE _APPLICATION _ERROR()

When using local procedures, you can trap these messages by writing an exception handler such as the following

Notice that the WHEN clause requires an exception name. If the exception does not have a name, for example, exceptions generated with RAISE _APPLICATION_ ERROR, you can assign one using PRAGMA _EXCEPTION _INIT. For example:

When calling a remote procedure, exceptions can be handled by an exception handler in the local procedure. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Note that PL/SQL user -defined exceptions always return ORA -06510 to the local procedure.

Therefore, it is not possible to distinguish between two different user-defined exceptions based on the error number. All other remote exceptions can be handled in the same manner as local exceptions.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics