DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00947 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00947: Not Enough Values — Causes, Fixes & Prevention

ORA-00947 is a common Oracle error thrown when the number of values provided in a DML statement (typically an INSERT) is fewer than the number of columns specified. In simple terms, Oracle expected more values than you gave it, and it refuses to proceed until the counts match. This error frequently appears after schema changes or during code refactoring when column lists and value lists fall out of sync.

Top 3 Causes

1. Mismatch Between Column List and VALUES Clause

The most frequent cause: you list more columns than you supply values for in the VALUES clause.

-- Causes ORA-00947: 3 columns, only 2 values
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe');

-- Fixed: column count matches value count
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (101, 'John Doe', 10);
Enter fullscreen mode Exit fullscreen mode

2. INSERT INTO ... SELECT with Too Few SELECT Columns

When using INSERT INTO ... SELECT, the SELECT clause must return exactly as many columns as are listed in the INSERT target column list.

-- Causes ORA-00947: INSERT expects 3 columns, SELECT returns 2
INSERT INTO employees (emp_id, emp_name, dept_id)
SELECT employee_id, first_name
FROM hr.employees
WHERE department_id = 10;

-- Fixed: SELECT returns 3 columns to match INSERT target
INSERT INTO employees (emp_id, emp_name, dept_id)
SELECT employee_id, first_name, department_id
FROM hr.employees
WHERE department_id = 10;

-- Tip: use a literal if a source column doesn't exist
INSERT INTO employees (emp_id, emp_name, dept_id)
SELECT employee_id, first_name, 99
FROM hr.employees
WHERE department_id = 10;
Enter fullscreen mode Exit fullscreen mode

3. PL/SQL Dynamic SQL or RECORD TYPE with Missing Fields

When building INSERT statements dynamically or using PL/SQL record types, it's easy to miss a field, causing a runtime ORA-00947.

-- Causes ORA-00947: only 2 values provided for 3-column INSERT
DECLARE
  v_id   NUMBER := 101;
  v_name VARCHAR2(100) := 'John Doe';
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (:1, :2)'
    USING v_id, v_name;  -- Missing third bind variable!
END;
/

-- Fixed: bind variables match column count
DECLARE
  v_id     NUMBER := 101;
  v_name   VARCHAR2(100) := 'John Doe';
  v_dept   NUMBER := 10;
BEGIN
  EXECUTE IMMEDIATE
    'INSERT INTO employees (emp_id, emp_name, dept_id) VALUES (:1, :2, :3)'
    USING v_id, v_name, v_dept;
END;
/

-- Safer PL/SQL approach using %ROWTYPE
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  v_emp.emp_id   := 101;
  v_emp.emp_name := 'John Doe';
  v_emp.dept_id  := 10;
  INSERT INTO employees VALUES v_emp;
  COMMIT;
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Count your columns and values — line them up side by side and verify the counts match exactly.
  • Never omit the column list — always explicitly name target columns in every INSERT statement.
  • After schema changes, search your codebase for all INSERT statements targeting the modified table and update them immediately.
  • Use %ROWTYPE in PL/SQL wherever possible — it automatically adapts when a table's structure changes.

Prevention Tips

1. Always Explicit Column Lists
Never write INSERT INTO table VALUES (...) without listing columns. When a new column is added to the table, an explicit column list will immediately surface any mismatch at the next execution, making the problem obvious and easy to fix.

-- Bad practice (fragile, breaks silently on schema changes)
INSERT INTO employees VALUES (101, 'John Doe', 10, SYSDATE);

-- Good practice (self-documenting and schema-change-resilient)
INSERT INTO employees (emp_id, emp_name, dept_id, hire_date)
VALUES (101, 'John Doe', 10, SYSDATE);
Enter fullscreen mode Exit fullscreen mode

2. Integrate DML Unit Tests into Your CI/CD Pipeline
Add automated tests that execute your key INSERT and INSERT INTO ... SELECT statements against a test schema after every deployment. Tools like utPLSQL or dbUnit can validate DML correctness before code reaches production, catching ORA-00947 and similar errors early in the development cycle.

Related Oracle Errors

Error Code Description
ORA-00913 Too many values — the opposite of ORA-00947
ORA-00932 Inconsistent datatypes — column count matches, but types don't
ORA-01400 Cannot insert NULL — often appears after fixing ORA-00947
ORA-00904 Invalid identifier — common alongside ORA-00947 after schema changes

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

Top comments (0)