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;
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;
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;
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;
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.');
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;
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;
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;
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;
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.');
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
How to Resolve ORA-01031: Insufficient Privileges
Resolving ORA-01034: Oracle Not Available
How to Resolve ORA-01403: No Data Found
ORA-01422: Exact Fetch Returns More Rows Than Requested
Resolving ORA-01461: Can Bind a LONG Value Only for Insert into a LONG Column
ORA-01555 Snapshot Too Old
ORA-01722: Invalid Number
Resolving ORA-01830: Date Format Picture Ends Before Converting Entire Input String
Resolving ORA-03113 End-of-File on Communication Channel
How to Resolve ORA-04030: Out of Process Memory
ORA-04031: Unable to Allocate X Bytes of Shared Memory
Resolving ORA-06508: PL/SQL: Could Not Find Program Unit Being Called
Resolving ORA-20000: Buffer Overflow
Resolving ORA-20001: Maximum Web Service Requests Exceeded
Resolving ORA-27101: Shared Memory Realm Does Not Exist
Resolving ORA-3135: Connection Lost Contact
Read more | Learn more
Cloud Technology
Read more | Learn more
Oracle Database
Read more | Learn more
MSSQL Database
Read more | Learn more
PostGres Database
Read more | Learn more
Linux
Read more | Learn more
ASP/C#
Read more | Learn more
Online Tests
Read more | Learn more
