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

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

Error Description

ORA-01427 occurs when a subquery that is expected to return only one row actually returns multiple rows. This error typically happens in SELECT statements, WHERE clauses, or when assigning subquery results to variables.

Complete Error Message

ORA-01427: single-row subquery returns more than one row

Severity Level

🟡 MEDIUM - Query execution error that prevents statement completion but doesn’t affect database operations.

Common Causes

1. Incorrect WHERE Clause Logic

• Missing or insufficient filtering conditions in subquery
• Using non-unique columns in single-row context
• Logical errors in join conditions

2. Data Issues

• Duplicate records in supposedly unique datasets
• Data integrity problems
• Missing unique constraints

3. Query Design Problems

• Using subqueries where correlated subqueries are needed
• Incorrect aggregation assumptions
• Missing GROUP BY clauses

4. Application Logic Errors

• Expecting unique results from non-unique data
• Incorrect parameter binding
• Race conditions creating duplicate records

Common Scenarios and Solutions

Scenario 1: Simple Subquery in SELECT Clause

Problem Example

-- This fails if an employee works in multiple departments
SELECT
  employee_id,
  first_name,
  (SELECT department_name
   FROM departments d
   WHERE d.department_id = e.department_id) as dept_name
FROM employees e;

-- Fails if department_id is not unique in departments table

Solutions

-- Solution 1: Use DISTINCT if duplicates are expected
SELECT
  employee_id,
  first_name,
  (SELECT DISTINCT department_name
   FROM departments d
   WHERE d.department_id = e.department_id) as dept_name
FROM employees e;

-- Solution 2: Use aggregate function to handle multiples
SELECT
  employee_id,
  first_name,
  (SELECT MAX(department_name)
   FROM departments d
   WHERE d.department_id = e.department_id) as dept_name
FROM employees e;

-- Solution 3: Use JOIN instead of subquery
SELECT
  e.employee_id,
  e.first_name,
  d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Solution 4: Use ROWNUM to limit results
SELECT
  employee_id,
  first_name,
  (SELECT department_name
   FROM departments d
   WHERE d.department_id = e.department_id
   AND ROWNUM = 1) as dept_name
FROM employees e;

Scenario 2: Subquery in WHERE Clause

Problem Example

-- This fails if multiple employees have the same salary
SELECT * FROM employees
WHERE salary = (SELECT salary FROM employees WHERE department_id = 10);

-- Fails if more than one employee in department 10

Solutions

-- Solution 1: Use IN instead of = for multiple values
SELECT * FROM employees
WHERE salary IN (SELECT salary FROM employees WHERE department_id = 10);

-- Solution 2: Use specific criteria to ensure single row
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department_id = 10);

-- Solution 3: Use EXISTS for existence check
SELECT * FROM employees e1
WHERE EXISTS (
  SELECT 1 FROM employees e2
  WHERE e2.department_id = 10
  AND e2.salary = e1.salary
);

-- Solution 4: Add additional filtering
SELECT * FROM employees
WHERE salary = (
  SELECT salary
  FROM employees
  WHERE department_id = 10
  AND employee_id = (
    SELECT MIN(employee_id)
    FROM employees
    WHERE department_id = 10
  )
);

Scenario 3: Subquery with Variable Assignment (PL/SQL)

Problem Example

DECLARE
  v_dept_name VARCHAR2(100);
BEGIN
  -- This fails if employee_id 100 has multiple department records
  SELECT department_name INTO v_dept_name
  FROM departments
  WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE employee_id = 100
  );
END;

Solutions

-- Solution 1: Use cursor with explicit handling
DECLARE
  v_dept_name VARCHAR2(100);
  CURSOR c_dept IS
    SELECT department_name
    FROM departments
    WHERE department_id = (
      SELECT department_id
      FROM employees
      WHERE employee_id = 100
    );
BEGIN
  OPEN c_dept;
  FETCH c_dept INTO v_dept_name;
  IF c_dept%NOTFOUND THEN
    v_dept_name := 'NOT FOUND';
  END IF;
  CLOSE c_dept;
END;
/

-- Solution 2: Use exception handling with aggregate
DECLARE
  v_dept_name VARCHAR2(100);
BEGIN
  SELECT MAX(department_name) INTO v_dept_name
  FROM departments
  WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE employee_id = 100
  );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_dept_name := 'NOT FOUND';
  WHEN TOO_MANY_ROWS THEN
    v_dept_name := 'MULTIPLE FOUND';
END;
/

-- Solution 3: Use ROWNUM limitation
DECLARE
  v_dept_name VARCHAR2(100);
BEGIN
  SELECT department_name INTO v_dept_name
  FROM (
    SELECT department_name
    FROM departments
    WHERE department_id = (
      SELECT department_id
      FROM employees
      WHERE employee_id = 100
    )
    ORDER BY department_name
  )
  WHERE ROWNUM = 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    v_dept_name := 'NOT FOUND';
END;
/

Diagnostic Queries

1. Identify Problematic Subqueries

-- Test if a subquery returns multiple rows
SELECT COUNT(*) as row_count
FROM (
  -- Your problematic subquery here
  SELECT department_id
  FROM employees
  WHERE salary > 50000
);

-- If count > 1, you have multiple rows

-- Find duplicate values in supposedly unique columns
SELECT
  department_id,
  COUNT(*) as count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 1;

2. Analyze Data Uniqueness

-- Check for duplicates in key columns
SELECT
  column_name,
  COUNT(*) as total_rows,
  COUNT(DISTINCT column_name) as unique_values,
  COUNT(*) - COUNT(DISTINCT column_name) as duplicates
FROM your_table
GROUP BY column_name
HAVING COUNT(*) > COUNT(DISTINCT column_name);

-- Find specific duplicate records
SELECT *
FROM (
  SELECT
    t.*,
    ROW_NUMBER() OVER (PARTITION BY key_column ORDER BY rowid) as rn
  FROM your_table t
)
WHERE rn > 1;

3. Validate Subquery Results

-- Create a test function to validate subquery uniqueness
CREATE OR REPLACE FUNCTION test_subquery_uniqueness(
  p_sql CLOB
) RETURN VARCHAR2 IS
  v_count NUMBER;
  v_sql CLOB := 'SELECT COUNT(*) FROM (' || p_sql || ')';
BEGIN
  EXECUTE IMMEDIATE v_sql INTO v_count;

  IF v_count = 0 THEN
    RETURN 'NO_DATA_FOUND';
  ELSIF v_count = 1 THEN
    RETURN 'SINGLE_ROW';
  ELSE
    RETURN 'MULTIPLE_ROWS (' || v_count || ')';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'ERROR: ' || SQLERRM;
END;
/

-- Test your subqueries
SELECT test_subquery_uniqueness('
  SELECT department_id
  FROM employees
  WHERE salary > 50000
') as result FROM dual;

Advanced Solutions

1. Using Analytic Functions

-- Use ROW_NUMBER() to handle duplicates
SELECT
  employee_id,
  first_name,
  (SELECT department_name
   FROM (
     SELECT department_name,
            ROW_NUMBER() OVER (ORDER BY department_name) as rn
     FROM departments d
     WHERE d.department_id = e.department_id
   )
   WHERE rn = 1) as dept_name
FROM employees e;

-- Use FIRST_VALUE to get consistent results
SELECT
  employee_id,
  first_name,
  (SELECT FIRST_VALUE(department_name)
           OVER (ORDER BY department_name)
   FROM departments d
   WHERE d.department_id = e.department_id
   AND ROWNUM = 1) as dept_name
FROM employees e;

2. Conditional Logic for Multiple Scenarios

-- Handle different cases based on row count
SELECT
  employee_id,
  first_name,
  CASE
    WHEN (SELECT COUNT(*)
          FROM departments d
          WHERE d.department_id = e.department_id) = 0
    THEN 'NO DEPARTMENT'
    WHEN (SELECT COUNT(*)
          FROM departments d
          WHERE d.department_id = e.department_id) = 1
    THEN (SELECT department_name
          FROM departments d
          WHERE d.department_id = e.department_id)
    ELSE 'MULTIPLE DEPARTMENTS'
  END as dept_info
FROM employees e;

3. Using Common Table Expressions (CTEs)

-- Use CTE to pre-process and deduplicate data
WITH unique_departments AS (
  SELECT
    department_id,
    MAX(department_name) as department_name  -- or use business logic
  FROM departments
  GROUP BY department_id
)
SELECT
  e.employee_id,
  e.first_name,
  ud.department_name
FROM employees e
LEFT JOIN unique_departments ud ON e.department_id = ud.department_id;

-- CTE with window functions for complex deduplication
WITH ranked_departments AS (
  SELECT
    department_id,
    department_name,
    ROW_NUMBER() OVER (
      PARTITION BY department_id
      ORDER BY created_date DESC, department_name
    ) as rn
  FROM departments
)
SELECT
  e.employee_id,
  e.first_name,
  rd.department_name
FROM employees e
LEFT JOIN ranked_departments rd ON e.department_id = rd.department_id AND rd.rn = 1;

Prevention Strategies

1. Data Integrity Constraints

-- Add unique constraints to prevent duplicates
ALTER TABLE departments
ADD CONSTRAINT uk_dept_name UNIQUE (department_name);

-- Add check constraints for business rules
ALTER TABLE employees
ADD CONSTRAINT ck_emp_dept CHECK (
  department_id IS NOT NULL AND
  department_id > 0
);

-- Create composite unique constraints
ALTER TABLE employee_departments
ADD CONSTRAINT uk_emp_dept UNIQUE (employee_id, department_id);

2. Defensive Query Design

-- Create a safe subquery function
CREATE OR REPLACE FUNCTION safe_single_value(
  p_sql CLOB,
  p_default VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
  v_result VARCHAR2(4000);
  v_count NUMBER;
BEGIN
  -- First check count
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || p_sql || ')' INTO v_count;

  IF v_count = 0 THEN
    RETURN p_default;
  ELSIF v_count = 1 THEN
    EXECUTE IMMEDIATE p_sql INTO v_result;
    RETURN v_result;
  ELSE
    -- Multiple rows - you can choose strategy
    EXECUTE IMMEDIATE p_sql || ' AND ROWNUM = 1' INTO v_result;
    RETURN v_result || ' (MULTIPLE)';
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'ERROR: ' || SUBSTR(SQLERRM, 1, 100);
END;
/

-- Usage example
SELECT
  employee_id,
  safe_single_value(
    'SELECT department_name FROM departments WHERE department_id = ' || department_id,
    'UNKNOWN'
  ) as dept_name
FROM employees;

3. Query Validation Tools

-- Create a query validation package
CREATE OR REPLACE PACKAGE query_validator AS
  TYPE string_array IS TABLE OF VARCHAR2(4000);

  FUNCTION validate_subquery(p_sql CLOB) RETURN VARCHAR2;
  FUNCTION suggest_fixes(p_sql CLOB) RETURN string_array;
END;
/

CREATE OR REPLACE PACKAGE BODY query_validator AS
  FUNCTION validate_subquery(p_sql CLOB) RETURN VARCHAR2 IS
    v_count NUMBER;
  BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM (' || p_sql || ')' INTO v_count;

    CASE
      WHEN v_count = 0 THEN RETURN 'NO_DATA_FOUND';
      WHEN v_count = 1 THEN RETURN 'VALID_SINGLE_ROW';
      ELSE RETURN 'TOO_MANY_ROWS (' || v_count || ')';
    END CASE;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'SYNTAX_ERROR: ' || SQLERRM;
  END;

  FUNCTION suggest_fixes(p_sql CLOB) RETURN string_array IS
    v_suggestions string_array := string_array();
  BEGIN
    v_suggestions.EXTEND(4);
    v_suggestions(1) := 'Use MAX() or MIN() aggregate function';
    v_suggestions(2) := 'Add DISTINCT to eliminate duplicates';
    v_suggestions(3) := 'Use ROW_NUMBER() with WHERE rn = 1';
    v_suggestions(4) := 'Convert to JOIN instead of subquery';

    RETURN v_suggestions;
  END;
END;
/

Application-Level Solutions

1. Java/JDBC Example

public class SafeSubqueryExecutor {

    public String executeSingleValueQuery(Connection conn, String sql)
        throws SQLException {

        // First, count the results
        String countSql = "SELECT COUNT(*) FROM (" + sql + ")";
        try (PreparedStatement ps = conn.prepareStatement(countSql)) {
            ResultSet rs = ps.executeQuery();
            rs.next();
            int count = rs.getInt(1);

            switch (count) {
                case 0:
                    return null; // No data found
                case 1:
                    // Execute original query
                    try (PreparedStatement ps2 = conn.prepareStatement(sql)) {
                        ResultSet rs2 = ps2.executeQuery();
                        rs2.next();
                        return rs2.getString(1);
                    }
                default:
                    // Multiple rows - handle according to business logic
                    String limitedSql = sql + " AND ROWNUM = 1";
                    try (PreparedStatement ps3 = conn.prepareStatement(limitedSql)) {
                        ResultSet rs3 = ps3.executeQuery();
                        if (rs3.next()) {
                            return rs3.getString(1) + " (MULTIPLE)";
                        }
                    }
                    return "ERROR";
            }
        }
    }
}

2. PL/SQL Error Handling Template

CREATE OR REPLACE PROCEDURE safe_query_execution AS
  v_result VARCHAR2(4000);
  v_employee_id NUMBER := 100;
BEGIN
  BEGIN
    -- Attempt the problematic query
    SELECT department_name INTO v_result
    FROM departments
    WHERE department_id = (
      SELECT department_id
      FROM employees
      WHERE employee_id = v_employee_id
    );

    DBMS_OUTPUT.PUT_LINE('Success: ' || v_result);

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No data found for employee ' || v_employee_id);

    WHEN TOO_MANY_ROWS THEN
      -- Handle multiple rows with business logic
      SELECT MAX(department_name) INTO v_result
      FROM departments
      WHERE department_id = (
        SELECT department_id
        FROM employees
        WHERE employee_id = v_employee_id
      );

      DBMS_OUTPUT.PUT_LINE('Multiple rows found, selected: ' || v_result);

    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
  END;
END;
/

Related Oracle Errors

• ORA-01403: No Data Found - Opposite problem (no rows returned)
• ORA-00904: Invalid Identifier - Column reference issues
• ORA-00936: Missing Expression - Syntax errors in subqueries
• ORA-01722: Invalid Number - Data type issues in subqueries

Best Practices

1. Always validate data uniqueness before using single-row subqueries
2. Use appropriate constraints to maintain data integrity
3. Implement defensive coding with exception handling
4. Consider using JOINs instead of subqueries when possible
5. Test queries with realistic data volumes and edge cases
6. Use analytic functions for complex row selection logic
7. Document business rules for handling multiple rows
8. Implement proper error handling in application code

Quick Troubleshooting Checklist

1. ✅ Identify the specific subquery causing the error
2. ✅ Test the subquery independently to see how many rows it returns
3. ✅ Check for duplicate data in the source tables
4. ✅ Determine the business rule for handling multiple rows
5. ✅ Choose appropriate solution: DISTINCT, MAX/MIN, ROWNUM, or JOIN
6. ✅ Add proper error handling for future occurrences
7. ✅ Consider data integrity constraints to prevent the issue

This error is often a symptom of either data quality issues or incorrect query logic, and the solution depends on the specific business requirements for handling multiple values.