ORA-00913 Too Many Values - Resolution Guide

ORA-00913: Too Many Values

Error Overview

Error Text: ORA-00913: too many values

This error occurs when an SQL statement provides more values than expected. It’s most commonly seen in INSERT statements where the number of values in the VALUES clause exceeds the number of columns, or in subqueries where the SELECT list has more columns than the outer query expects.

Common Causes

1. INSERT with Mismatched Column Count

• More values in VALUES clause than columns specified
• Column list doesn’t match the values provided
• Copy-paste errors adding extra values

2. Subquery Returns Too Many Columns

• WHERE clause subquery returns multiple columns
• SET clause subquery returns more than one column
• Comparison operators used with multi-column subquery

3. UNION/UNION ALL Column Mismatch

• Different number of columns in UNION branches
• Added a column to one SELECT but not all others

4. INSERT…SELECT Mismatch

• SELECT returns more columns than the target INSERT column list

Diagnostic Examples

Example 1: INSERT Column Mismatch

-- This table has 3 columns
CREATE TABLE employees (id NUMBER, name VARCHAR2(100), dept VARCHAR2(50));

-- ERROR: 4 values for 3 columns
INSERT INTO employees VALUES (1, 'John Smith', 'IT', 'Active');
-- ORA-00913: too many values

-- FIX: Match values to columns
INSERT INTO employees VALUES (1, 'John Smith', 'IT');

-- BETTER: Always specify column list explicitly
INSERT INTO employees (id, name, dept) VALUES (1, 'John Smith', 'IT');

Example 2: Subquery with Too Many Columns

-- ERROR: Subquery returns 2 columns, comparison expects 1
SELECT * FROM orders
WHERE customer_id = (SELECT customer_id, customer_name FROM customers WHERE email = '[email protected]');
-- ORA-00913: too many values

-- FIX: Return only the needed column
SELECT * FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE email = '[email protected]');

Example 3: UNION Column Count Mismatch

-- ERROR: First SELECT has 3 columns, second has 4
SELECT id, name, salary FROM employees
UNION ALL
SELECT id, name, salary, department FROM contractors;
-- ORA-00913: too many values

-- FIX: Match column counts
SELECT id, name, salary FROM employees
UNION ALL
SELECT id, name, salary FROM contractors;

Example 4: INSERT…SELECT Mismatch

-- ERROR: Selecting 4 columns into 3-column insert
INSERT INTO emp_archive (id, name, dept)
SELECT id, name, dept, hire_date FROM employees WHERE status = 'INACTIVE';
-- ORA-00913: too many values

-- FIX: Match SELECT columns to INSERT columns
INSERT INTO emp_archive (id, name, dept)
SELECT id, name, dept FROM employees WHERE status = 'INACTIVE';

Diagnostic Queries

Check Table Column Count

-- Count columns in the target table
SELECT table_name, COUNT(*) as column_count
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
GROUP BY table_name;

-- List all columns with order
SELECT column_name, column_id, data_type, nullable
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
ORDER BY column_id;

Check for Virtual or Hidden Columns

-- Virtual columns count toward column total but shouldn't be in INSERT
SELECT column_name, data_type, virtual_column, hidden_column
FROM dba_tab_cols
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
ORDER BY column_id;

-- Only show insertable columns
SELECT column_name, column_id
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
  AND virtual_column = 'NO'
ORDER BY column_id;

Resolution Steps

Step 1: Identify the Mismatch

-- For INSERT statements, count both sides:
-- Left side: columns in INSERT INTO table (col1, col2, ...)
-- Right side: values in VALUES (val1, val2, ...)
-- These counts MUST match

-- If no column list specified, count ALL table columns
SELECT COUNT(*) as total_columns
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE';

Step 2: Always Use Explicit Column Lists

-- BAD: Fragile, breaks when table structure changes
INSERT INTO employees VALUES (1, 'John', 'IT', SYSDATE);

-- GOOD: Explicit, self-documenting, resilient
INSERT INTO employees (id, name, department, hire_date)
VALUES (1, 'John', 'IT', SYSDATE);

Step 3: Validate Subquery Column Counts

-- Run the subquery independently to check output
SELECT customer_id, customer_name  -- Returns 2 columns
FROM customers
WHERE email = '[email protected]';

-- If using = operator, subquery must return exactly 1 column
-- If using IN operator, subquery must return exactly 1 column
-- For multi-column comparisons, use row syntax:
SELECT * FROM orders
WHERE (customer_id, region_id) IN
  (SELECT customer_id, region_id FROM preferred_customers);

Prevention Strategies

1. Always Specify Column Lists in INSERT

-- This practice prevents ORA-00913 and ORA-00947 (not enough values)
-- It also survives table alterations that add new columns

INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES (seq_orders.NEXTVAL, :cust_id, SYSDATE, :total);

2. Use IDE Column Count Validation

-- Most IDEs highlight column count mismatches
-- SQL Developer, DataGrip, and Toad all provide this
-- Enable "check SQL syntax" before execution

3. Template for Bulk Inserts

-- When building bulk INSERT statements, generate from metadata
SELECT 'INSERT INTO ' || table_name || ' (' ||
  LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) ||
  ') VALUES (' ||
  LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id) ||
  ');'
FROM dba_tab_columns
WHERE owner = 'YOUR_SCHEMA'
  AND table_name = 'YOUR_TABLE'
GROUP BY table_name;

Related Errors

• ORA-00947: Not Enough Values - Opposite problem: fewer values than columns
• ORA-00936: Missing Expression - Missing value in expression list
• ORA-01400: Cannot Insert NULL - Required column missing from INSERT
• ORA-00904: Invalid Identifier - Column name typo in INSERT column list