It will be very difficult to create applications that are easy to use and debug unless you take a consistent, high-quality approach to dealing with errors.
Oracle PL/SQL’s error management capabilities allow you to define, raise and handle errors in very flexible ways. Limitations in its approach, however, mean that you will usually want to supplement the built-in features with your own application-specific code and tables.
I suggest that you meet this challenge by taking the following steps:
Study and understand how error raising and handling work in PL/SQL. It is not all completely intuitive. A prime example: an exception raised in the declaration section will not be handled by the exception section of that block.
Decide on the overall error management approach you will take in your application. Where and when do you handle errors? What information do you need to save and how will you do that? How are exceptions propagated to the host environment? How will you handle deliberate, unfortunate, and unexpected errors?
Build a standard framework to be used by all developers; that framework will include underlying tables, packages, and perhaps object types, along with a well-defined process for using these elements. Don’t resign yourself to PL/SQL’s limitations. Work around them by enhancing the error management model.
Create templates that everyone on your team can use, making it easier to follow the standard than to write one’s own error-handling ...