Скачать или смотреть Handling Two Exceptions with the Same Code in Oracle PL/SQL

Discover how to effectively manage two exceptions in Oracle PL/SQL when faced with the challenge of having them share the same error code. Learn the best practices for implementing user-defined exceptions while improving your database handling techniques.
---
This video is based on the question https://stackoverflow.com/q/65249521/ asked by the user 'readJohn' ( https://stackoverflow.com/u/13383185/ ) and on the answer https://stackoverflow.com/a/65249977/ provided by the user 'APC' ( https://stackoverflow.com/u/146325/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: two exceptions in the same module

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Handling Two Exceptions with the Same Code in Oracle PL/SQL: A Comprehensive Guide

When coding in Oracle PL/SQL, developers often encounter scenarios where they need to handle multiple exceptional cases. A common issue arises when two exceptions in the same module are assigned the same error code, leading to confusion and errors in handling. For instance, you might want to distinguish between missing values for "name" and "age" but find that the system raises the same error code for both. In this post, we’ll explore how to effectively manage such situations within PL/SQL.

The Problem

In PL/SQL, when you define exceptions, each one typically corresponds to a specific situation or error. However, if two different exceptions share the same error code, the PL/SQL engine becomes uncertain about which exception corresponds to which condition. This is problematic as it disrupts your error handling logic.

For instance, consider this snippet of code:

[[See Video to Reveal this Text or Code Snippet]]

The error arises because both name_excep and age_excep have been initialized with the same code, which is 100 (NO_DATA_FOUND). This leads to a compilation error that states, "You must put name_excep and age_excep in the same exception."

So, how do we resolve this?

Solutions for Handling Both Exceptions

To address this challenge, there are a couple of approaches you can take. Let’s break them down:

Option 1: Use Unique Error Codes for Exceptions

The best practice is to define unique error codes for each exception. Oracle allows user-defined exceptions to use codes in the range between -20000 to -20999, ensuring that each exception is handled distinctly. Here’s how to redefine the exceptions:

[[See Video to Reveal this Text or Code Snippet]]

Now, the exceptions can be effectively distinguished, and you can include the respective handling as follows:

[[See Video to Reveal this Text or Code Snippet]]

Option 2: Use a Single Exception with Conditional Logic

If you want to handle both conditions through a single exception, you can define a more generic exception and then apply conditional logic to determine which issue occurred. Here's an example:

[[See Video to Reveal this Text or Code Snippet]]

Then, adjust the exception block like this:

[[See Video to Reveal this Text or Code Snippet]]

Though this method works, it is less concise and can lead to clutter if you have many such conditions to check.

Best Practices for Exception Handling

Lastly, while printing out error messages using DBMS_OUTPUT.PUT_LINE is useful for debugging, it is generally considered bad practice in production systems. Here’s why:

Visibility: The messages can be suppressed or not visible to end users in certain environments.

Logging: Instead of directly printing messages, consider logging errors to a database or a logging file. This allows developers to track issues without exposing users to raw error messages.

Re-raise Exceptions: It’s often better to re-raise exceptions to allow the calling program or user to decide on further handling.

Conclusion

In summary, handling exceptions in PL/SQL, especially when error codes overlap, can be tricky. By following the steps outlined above, especially utilizing unique codes for user-defined exceptions, you can streamline your error handling while avoiding unnecessary complications. Ensuring proper practices in exception management not only improves code maintainability but enhances overall application robustness.

Whether you opt for unique exceptions or a more generalized approach, understanding the underlying rules of PL/SQL exception handling is crucial. Remember to choose what