Resolving the ORA-01422 Error in Oracle: Using MERGE Effectively

Описание: Learn how to efficiently handle multiple rows in Oracle procedures using `MERGE` statements without hitting the `ORA-01422` error.
---
This video is based on the question https://stackoverflow.com/q/76834124/ asked by the user 'js u' ( https://stackoverflow.com/u/15608508/ ) and on the answer https://stackoverflow.com/a/76834178/ provided by the user 'MT0' ( https://stackoverflow.com/u/1509264/ ) 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: Oracle: multiple rows in INTO variable in procedure

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.
---
Understanding the Problem: ORA-01422 Error in Oracle

When working with Oracle procedures, you may encounter an error that states: "ORA-01422: exact fetch returns more than requested number of rows." This error typically arises when you attempt to select multiple rows into scalar variables. In the case provided, the user was trying to fetch data from the DAILY_VISITOR table and update or insert it into the ALL_VISITOR table using a procedure.

The user's initial approach was as follows:

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

However, this approach leads to the aforementioned error because the SELECT ... INTO syntax is designed to handle only a single row. When the query returns multiple rows, Oracle doesn't know which row to fetch, resulting in an error.

An Effective Solution: Using MERGE Statement

To resolve this issue and effectively handle multiple rows of data, we can leverage the MERGE statement directly in SQL, bypassing the need for PL/SQL constructs that may complicate the process. Here’s how to do it:

The Enhanced MERGE Statement

Below is the optimized solution using the MERGE statement without the need for intermediate variables:

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

Breaking Down the Solution

MERGE INTO: This statement allows you to combine the UPDATE and INSERT operations, effectively handling both scenarios of whether a match is found based on the specified condition.

USING Clause: Here, we select data directly from the DAILY_VISITOR table, which serves as our source data for the merge operation.

ON Clause: This defines the criteria for matching records. In this case, it's matching REG_NO from both tables.

WHEN MATCHED Clause: This part specifies what to do when there is a match. It updates the existing entry in ALL_VISITOR with the corresponding values from DAILY_VISITOR.

WHEN NOT MATCHED Clause: This clause tells Oracle to insert a new record into ALL_VISITOR if no match is found with the specified condition.

Why This Approach?

Efficiency: This solution reduces the need for separate SELECT and INSERT/UPDATE operations and works more efficiently by processing data in bulk.

Simplicity: It avoids the complexities of handling multiple rows in PL/SQL and rather utilizes native SQL capabilities, making your code cleaner and easier to maintain.

Error Prevention: By using MERGE, you eliminate the risk of the ORA-01422 error because you’re no longer attempting to select multiple rows into variables.

Conclusion

By utilizing the enhanced MERGE statement, you can effectively manage and manipulate multiple rows of data in Oracle without the hassle of encountering the ORA-01422 error. This approach not only simplifies your code but also enhances performance by executing the operation in a single SQL statement. If you're working with multiple rows in your Oracle database, consider adopting this efficient method for your data operations.