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