ORA-01422: Exact Fetch Returns More Rows Than Requested

Introduction
Understanding the Cause
Step-by-Step Solutions
Examples
Conclusion

Introduction

The ORA-01422 error in Oracle indicates that a query returned more rows than expected. This typically occurs when a SELECT INTO statement retrieves more than one row, but the expectation was to fetch exactly one row. Understanding the causes and learning how to resolve this error is crucial for maintaining smooth database operations.

Understanding the Cause

The ORA-01422 error occurs when:
  • Multiple Rows Returned: A SELECT INTO statement returns more than one row.
  • Incorrect Query Assumptions: The query assumes a unique result, but the actual data has multiple matching rows.
  • Data Inconsistency: The data might not be unique, leading to multiple rows being fetched.

Step-by-Step Solutions

Here are several methods to resolve the ORA-01422 error:

1. Use Aggregation Functions

If you expect a single value from multiple rows, use aggregation functions like MAX, MIN, SUM, or AVG.


BEGIN
    SELECT MAX(salary)
    INTO v_max_salary
    FROM employees
    WHERE department_id = 10;
END;

2. Use a CURSOR for Multiple Rows

If you need to handle multiple rows, use a CURSOR to fetch each row individually.


DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 10;
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    CLOSE emp_cursor;
END;

3. Add Filtering Criteria

Ensure your query is specific enough to return only one row by adding more conditions to your WHERE clause.


BEGIN
    SELECT employee_id, first_name, last_name
    INTO v_emp_id, v_first_name, v_last_name
    FROM employees
    WHERE department_id = 10
      AND ROWNUM = 1;
END;

4. Use SELECT INTO with EXCEPTION Handling

Handle the situation where multiple rows are returned using the TOO_MANY_ROWS exception.


BEGIN
    SELECT employee_id, first_name, last_name
    INTO v_emp_id, v_first_name, v_last_name
    FROM employees
    WHERE department_id = 10;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
END;

Examples

Example 1: Handling Multiple Rows with Aggregation

If you want to fetch the highest salary from a department:


BEGIN
    SELECT MAX(salary)
    INTO v_max_salary
    FROM employees
    WHERE department_id = 20;
END;

Example 2: Using CURSOR for Multiple Rows

If you need to process all employees in a department:


DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 20;
    v_emp_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_first_name, v_last_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp_id || ' ' || v_first_name || ' ' || v_last_name);
    END LOOP;
    CLOSE emp_cursor;
END;

Example 3: Using Specific Filtering Criteria

If you want to ensure only one employee is returned:


BEGIN
    SELECT employee_id, first_name, last_name
    INTO v_emp_id, v_first_name, v_last_name
    FROM employees
    WHERE department_id = 20
      AND ROWNUM = 1;
END;

Example 4: Using SELECT INTO with Exception Handling

Handling cases where the query might return multiple rows:


BEGIN
    SELECT employee_id, first_name, last_name
    INTO v_emp_id, v_first_name, v_last_name
    FROM employees
    WHERE department_id = 20;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
END;

Conclusion

The ORA-01422 error in Oracle is encountered when a query returns more rows than expected. By understanding its causes and applying the appropriate solutions, database administrators and developers can effectively manage and prevent this error. Regularly reviewing query criteria, using CURSOR for multiple rows, and incorporating exception handling are essential practices to maintain smooth database operations.

Related content



Rate Your Experience

: 89 : 1


Last updated in July, 2024

Online Tests
Read more

Cloud Technology
Read more

Oracle Database
Read more

MSSQL Database
Read more

PostGres Database
Read more

Linux
Read more

ASP/C#
Read more

Quick Access

All Rights Reserved @2024 | www.dbdocs.net | Privacy Policy | Disclaimer | Cookie Policy