Oracle ORA-00913 Error: Causes and Solutions Complete Guide

ORA-00913: Too Many Values — Causes, Fixes & Prevention

ORA-00913 is a common Oracle SQL parsing error that occurs when the number of values provided in a SQL statement exceeds the number of columns or expressions expected. Oracle detects this mismatch at parse time, meaning the statement fails immediately before any data is touched. Understanding the root causes helps you resolve and prevent this error efficiently.

---

Top 3 Causes

1. Mismatch in INSERT ... VALUES Statement

The most frequent cause: the number of values in the VALUES clause exceeds the number of columns listed in the INSERT statement.

-- ❌ Wrong: 3 columns specified, but 4 values provided
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe', 10, 'New York');

-- ✅ Fix 1: Remove the extra value
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe', 10);

-- ✅ Fix 2: Add the missing column to the column list
INSERT INTO employees (emp_id, emp_name, dept_id, location)
VALUES (101, 'John Doe', 10, 'New York');

2. Subquery Returns Too Many Columns

When a subquery used in a single-value context (e.g., = operator) returns more than one column, Oracle raises ORA-00913.

-- ❌ Wrong: Subquery returns two columns but only one is expected
SELECT *
FROM employees
WHERE dept_id = (SELECT dept_id, dept_name FROM departments WHERE location = 'NYC');

-- ✅ Fix 1: Limit the subquery to a single column
SELECT *
FROM employees
WHERE dept_id = (SELECT dept_id FROM departments WHERE location = 'NYC');

-- ✅ Fix 2: Use EXISTS for multi-column conditions
SELECT e.*
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
      AND d.location = 'NYC'
);

-- ✅ Fix 3: Use row value constructor for multi-column IN comparison
SELECT *
FROM employees
WHERE (dept_id, job_id) IN (
    SELECT dept_id, job_id FROM departments WHERE location = 'NYC'
);

3. INSERT INTO ... SELECT Column Count Mismatch

When using INSERT INTO ... SELECT, the SELECT clause returns more columns than the INSERT target specifies.

-- ❌ Wrong: SELECT returns 4 columns, INSERT expects 3
INSERT INTO emp_backup (emp_id, emp_name, dept_id)
SELECT emp_id, emp_name, dept_id, salary FROM employees;

-- ✅ Fix 1: Match SELECT columns to INSERT columns
INSERT INTO emp_backup (emp_id, emp_name, dept_id)
SELECT emp_id, emp_name, dept_id FROM employees;

-- ✅ Fix 2: Include all columns in both lists
INSERT INTO emp_backup (emp_id, emp_name, dept_id, salary)
SELECT emp_id, emp_name, dept_id, salary FROM employees;

-- ✅ Validation tip: Check column counts before bulk insert
SELECT COUNT(*) FROM all_tab_columns WHERE table_name = 'EMPLOYEES' AND owner = 'HR';
SELECT COUNT(*) FROM all_tab_columns WHERE table_name = 'EMP_BACKUP' AND owner = 'HR';

---

Quick Fix Checklist

• Count columns in your INSERT target list and match them to VALUES or SELECT.
• Never use SELECT * in INSERT INTO ... SELECT statements.
• Reduce subquery SELECT to only the column(s) needed for comparison.
• Use IN or EXISTS when comparing against multi-column subquery results.

---

Prevention Tips

1. Always use explicit column lists — Avoid INSERT INTO table VALUES (...) without a column list and avoid SELECT * in production DML. Explicit columns make your code resilient to schema changes and immediately expose mismatches during code review.

2. Validate DML in staging before production deployment — Whenever a DDL change (adding/dropping columns) is deployed, run a regression check on all INSERT statements and subqueries referencing the affected table. Use ALL_DEPENDENCIES to find dependent objects quickly.

---

Related Errors

Error Code	Description
ORA-00947	Not enough values — the opposite of ORA-00913
ORA-01427	Single-row subquery returns more than one row

ORA-00913 is about column count, while ORA-01427 is about row count — keep both in mind when writing subqueries.

---

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