Oracle ORA-01427 Error: Causes and Solutions Complete Guide

ORA-01427: Single-Row Subquery Returns More Than One Row

ORA-01427 is one of the most common Oracle errors encountered by developers and DBAs alike. It occurs when a subquery used in a context that expects a single value (scalar) returns two or more rows instead. Oracle immediately raises this exception because it cannot assign multiple values to a single variable, column reference, or comparison expression.

---

Top 3 Causes

1. Scalar Subquery in SELECT or WHERE Clause Returns Multiple Rows

When you use a subquery with the = operator, Oracle expects exactly one row back. If the referenced table has duplicate or multiple matching records, ORA-01427 is thrown.

-- Problematic query (ORA-01427)
SELECT employee_id,
       (SELECT department_name
        FROM departments
        WHERE location_id = 1700) AS dept_name
FROM employees;

-- Fix: Use an aggregate function to guarantee one row
SELECT employee_id,
       (SELECT MAX(department_name)
        FROM departments
        WHERE location_id = 1700) AS dept_name
FROM employees;

-- Fix: Use FETCH FIRST (Oracle 12c+)
SELECT employee_id,
       (SELECT department_name
        FROM departments
        WHERE location_id = 1700
        ORDER BY department_id
        FETCH FIRST 1 ROW ONLY) AS dept_name
FROM employees;

---

2. UPDATE SET Clause Subquery Returns Multiple Rows

Using a subquery in an UPDATE statement's SET clause is a frequent source of ORA-01427, especially in batch jobs or data migration scripts where referencing tables grow over time.

-- Problematic UPDATE (ORA-01427)
UPDATE employees e
SET e.salary = (SELECT salary
                FROM salary_history sh
                WHERE sh.employee_id = e.employee_id);

-- Fix: Use MAX() to return only the highest (or most recent) salary
UPDATE employees e
SET e.salary = (SELECT MAX(sh.salary)
                FROM salary_history sh
                WHERE sh.employee_id = e.employee_id);

---

3. Missing or Incomplete WHERE Condition in Subquery

A subquery without proper correlation conditions or a complete WHERE clause can unintentionally scan the entire table and return all rows, triggering ORA-01427.

-- Problematic query (missing correlation - returns all department IDs)
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id = (SELECT d.department_id
                          FROM departments d
                          WHERE d.manager_id > 100);

-- Fix 1: Switch = to IN for multi-row subqueries
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id
                           FROM departments d
                           WHERE d.manager_id > 100);

-- Fix 2: Use EXISTS for better performance
SELECT e.employee_id, e.employee_name
FROM employees e
WHERE EXISTS (SELECT 1
              FROM departments d
              WHERE d.department_id = e.department_id
              AND d.manager_id > 100);

---

Quick Fix Summary

Situation	Recommended Fix
Scalar subquery with =	Use MAX(), MIN(), or FETCH FIRST 1 ROW ONLY
Multi-row comparison	Replace = with IN or use EXISTS
UPDATE SET subquery	Add aggregate function or tighter WHERE condition
PL/SQL SELECT INTO	Use SELECT INTO with proper exception handling

---

Prevention Tips

1. Always validate your subquery independently before embedding it.
Run the subquery alone and check the row count with SELECT COUNT(*). If it returns more than one row and you are using =, you need to either add constraints or switch operators. Make it a team coding standard to never use = with a subquery unless uniqueness is guaranteed by a PRIMARY KEY or UNIQUE constraint.

2. Apply UNIQUE or PRIMARY KEY constraints on reference columns.
The most robust long-term prevention is enforcing uniqueness at the data model level. If a subquery references a column that should always return one row, that column should have a UNIQUE or PRIMARY KEY constraint defined. This eliminates the root cause rather than just patching the SQL.

---

Related Errors

• ORA-01422: Raised in PL/SQL when SELECT INTO fetches more than one row — the PL/SQL equivalent of ORA-01427.
• ORA-01403: NO_DATA_FOUND — the opposite problem, where a single-row fetch returns no rows at all. Always handle both ORA-01422 and ORA-01403 together in PL/SQL exception blocks.

---

📖 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.