Oracle ORA-01422 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01422: Exact Fetch Returns More Than Requested Number of Rows

ORA-01422 is a common PL/SQL error that occurs when a SELECT INTO statement retrieves more than one row from the database. Oracle's SELECT INTO is designed to handle exactly one row — if the query returns two or more rows, Oracle immediately raises this exception. This error is especially tricky because code may work perfectly in development with limited data, then suddenly break in production as data grows.

Top 3 Causes

1. Insufficient WHERE Clause (Most Common)

Using a non-unique column in the WHERE clause allows multiple rows to match the condition.

-- PROBLEM: Multiple employees can share the same department
DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE dept_id = 10; -- Returns multiple rows if dept has many employees!
END;
/

-- FIX: Use a Primary Key or Unique column
DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE emp_id = 1001; -- Guaranteed single row
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END;
/

2. Missing Unique Constraints Causing Duplicate Data

When tables lack proper Primary Key or Unique constraints, duplicate records can be inserted silently, causing SELECT INTO to fail unexpectedly.

-- Check for duplicate data before querying
SELECT emp_id, COUNT(*)
  FROM employees
 GROUP BY emp_id
HAVING COUNT(*) > 1;

-- Safe approach: Add exception handling immediately
DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE emp_id = 1001;
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Duplicate records found. Investigate data integrity.');
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: No record found for emp_id = 1001.');
END;
/

3. Wrong Use of SELECT INTO Instead of a Cursor

Developers sometimes use SELECT INTO when the business logic actually requires processing multiple rows. This works until data conditions change.

-- PROBLEM: Assumes only one active order per customer — wrong assumption
DECLARE
  v_order_id NUMBER;
BEGIN
  SELECT order_id
    INTO v_order_id
    FROM orders
   WHERE customer_id = 500
     AND status = 'ACTIVE'; -- Customer may have multiple active orders!
END;
/

-- FIX: Use a cursor FOR loop to handle multiple rows properly
BEGIN
  FOR order_rec IN (
    SELECT order_id, order_date, total_amount
      FROM orders
     WHERE customer_id = 500
       AND status = 'ACTIVE'
     ORDER BY order_date DESC
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('Order ID: ' || order_rec.order_id
                      || ' | Date: ' || order_rec.order_date
                      || ' | Amount: ' || order_rec.total_amount);
  END LOOP;
END;
/

Quick Fix Solutions

Option 1 — Restrict to one row using ROWNUM (temporary workaround only):

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE dept_id = 10
     AND ROWNUM = 1; -- Use only as a temporary patch, not a permanent fix
END;
/

Option 2 — Oracle 12c+ FETCH FIRST syntax:

DECLARE
  v_name VARCHAR2(100);
BEGIN
  SELECT emp_name
    INTO v_name
    FROM employees
   WHERE dept_id = 10
   FETCH FIRST 1 ROWS ONLY;
END;
/

⚠️ Warning: These workarounds silence the error but do not fix the root cause. Always investigate why multiple rows exist.

Prevention Tips

Always handle both TOO_MANY_ROWS and NO_DATA_FOUND exceptions in every PL/SQL block that uses SELECT INTO. Make this a mandatory team coding standard enforced during code reviews.
Enforce Primary Key and Unique constraints at the database design level. Never rely solely on application logic to guarantee uniqueness. Run periodic data quality audits to detect duplicates before they cause runtime errors:

-- Periodic duplicate detection query
SELECT key_column, COUNT(*) AS duplicates
  FROM your_table
 GROUP BY key_column
HAVING COUNT(*) > 1
 ORDER BY duplicates DESC;

Related Errors

ORA-01403 (NO_DATA_FOUND): The opposite of ORA-01422 — occurs when SELECT INTO returns zero rows. Always handle both exceptions together.
ORA-06512: A stack trace error that accompanies unhandled PL/SQL exceptions, frequently seen alongside ORA-01422 in error logs.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.