ORA-00947 Not Enough Values

ORA-00947: Not Enough Values

Error Overview

Error Text: ORA-00947: not enough values

The ORA-00947 error occurs when an INSERT statement provides fewer values than the number of columns in the target table or column list. Oracle requires a value for each column being inserted into, unless the column has a default value or allows NULL.

Common Causes

1. Column Count Mismatch

• INSERT statement missing values
• Table structure changed (columns added)
• Copy/paste errors in SQL statements

2. Missing Column List

• Using INSERT without explicit column list
• Table has more columns than expected
• Hidden columns or virtual columns

3. Subquery Issues

• SELECT in INSERT…SELECT returns fewer columns
• UNION queries with mismatched column counts
• Subquery column order doesn’t match

4. Dynamic SQL Problems

• Generated SQL with incomplete value lists
• Application code building SQL incorrectly
• Parameter binding errors

Diagnostic Queries

Check Table Structure

-- List all columns in the table
SELECT
  column_name,
  column_id,
  data_type,
  data_length,
  nullable,
  data_default
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
ORDER BY column_id;

-- Count columns
SELECT COUNT(*) as column_count
FROM user_tab_columns
WHERE table_name = UPPER('&table_name');

-- Check for virtual/hidden columns (12c+)
SELECT
  column_name,
  column_id,
  hidden_column,
  virtual_column
FROM user_tab_cols
WHERE table_name = UPPER('&table_name')
ORDER BY column_id;

Identify Required Columns

-- Columns that require values (NOT NULL without default)
SELECT column_name, data_type
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
  AND nullable = 'N'
  AND data_default IS NULL
ORDER BY column_id;

-- All columns with their requirements
SELECT
  column_name,
  data_type,
  CASE
    WHEN nullable = 'N' AND data_default IS NULL THEN 'REQUIRED'
    WHEN nullable = 'N' AND data_default IS NOT NULL THEN 'HAS DEFAULT'
    ELSE 'OPTIONAL'
  END as requirement
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
ORDER BY column_id;

Resolution Steps

1. Add Explicit Column List

-- BAD: No column list - relies on table structure
INSERT INTO employees VALUES (100, 'John', 'Doe');

-- GOOD: Explicit column list
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (100, 'John', 'Doe');

-- GOOD: All columns explicitly listed
INSERT INTO employees (
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  department_id
) VALUES (
  100,
  'John',
  'Doe',
  '[email protected]',
  SYSDATE,
  10
);

2. Provide Missing Values

-- If table has 5 columns, provide 5 values
-- Check table structure first
DESC employees;

-- Then provide all values
INSERT INTO employees VALUES (
  100,              -- employee_id
  'John',           -- first_name
  'Doe',            -- last_name
  '[email protected]', -- email
  SYSDATE           -- hire_date
);

3. Use NULL or DEFAULT for Optional Columns

-- Using NULL explicitly
INSERT INTO employees (
  employee_id, first_name, last_name, email, phone, hire_date
) VALUES (
  100, 'John', 'Doe', '[email protected]', NULL, SYSDATE
);

-- Using DEFAULT keyword
INSERT INTO employees (
  employee_id, first_name, last_name, email, status, hire_date
) VALUES (
  100, 'John', 'Doe', '[email protected]', DEFAULT, SYSDATE
);

-- Omit optional columns from column list
INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (100, 'John', 'Doe', SYSDATE);

4. Fix INSERT…SELECT Statements

-- BAD: Subquery returns fewer columns than target
INSERT INTO employees_archive
SELECT employee_id, first_name, last_name
FROM employees;

-- GOOD: Match column counts
INSERT INTO employees_archive (employee_id, first_name, last_name)
SELECT employee_id, first_name, last_name
FROM employees;

-- GOOD: Select all needed columns
INSERT INTO employees_archive
SELECT
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  salary,
  department_id
FROM employees;

-- GOOD: Use NULL or literals for missing columns
INSERT INTO employees_archive
SELECT
  employee_id,
  first_name,
  last_name,
  email,
  hire_date,
  salary,
  NULL as manager_id,    -- Column not in source
  SYSDATE as archive_date -- Additional column
FROM employees;

5. Handle Dynamic SQL

-- Build INSERT with explicit columns
CREATE OR REPLACE PROCEDURE insert_employee(
  p_id NUMBER,
  p_fname VARCHAR2,
  p_lname VARCHAR2
) AS
  v_sql VARCHAR2(1000);
BEGIN
  -- Always specify columns in dynamic SQL
  v_sql := 'INSERT INTO employees (employee_id, first_name, last_name) ' ||
           'VALUES (:1, :2, :3)';

  EXECUTE IMMEDIATE v_sql USING p_id, p_fname, p_lname;
  COMMIT;
END;
/

-- Generate column list dynamically
CREATE OR REPLACE FUNCTION get_insert_columns(p_table VARCHAR2)
RETURN VARCHAR2 AS
  v_columns VARCHAR2(4000);
BEGIN
  SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
  INTO v_columns
  FROM user_tab_columns
  WHERE table_name = UPPER(p_table);

  RETURN v_columns;
END;
/

Prevention Strategies

1. Always Use Explicit Column Lists

-- Template for INSERT statements
INSERT INTO table_name (
  column1,
  column2,
  column3
) VALUES (
  value1,
  value2,
  value3
);

-- This survives table structure changes

2. Create Insert Procedure

-- Encapsulate inserts in procedures
CREATE OR REPLACE PROCEDURE ins_employee(
  p_id         NUMBER,
  p_first_name VARCHAR2,
  p_last_name  VARCHAR2,
  p_email      VARCHAR2 DEFAULT NULL,
  p_hire_date  DATE DEFAULT SYSDATE
) AS
BEGIN
  INSERT INTO employees (
    employee_id,
    first_name,
    last_name,
    email,
    hire_date
  ) VALUES (
    p_id,
    p_first_name,
    p_last_name,
    p_email,
    p_hire_date
  );
END;
/

-- Call with named parameters
EXEC ins_employee(p_id => 100, p_first_name => 'John', p_last_name => 'Doe');

3. Validate Before Execute

-- Check column count before dynamic insert
CREATE OR REPLACE PROCEDURE safe_insert(
  p_table VARCHAR2,
  p_values VARCHAR2  -- Comma-separated values
) AS
  v_table_cols NUMBER;
  v_value_count NUMBER;
BEGIN
  -- Count table columns
  SELECT COUNT(*) INTO v_table_cols
  FROM user_tab_columns
  WHERE table_name = UPPER(p_table);

  -- Count values (simple comma count + 1)
  v_value_count := LENGTH(p_values) - LENGTH(REPLACE(p_values, ',', '')) + 1;

  IF v_value_count != v_table_cols THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Column count (' || v_table_cols || ') does not match value count (' || v_value_count || ')');
  END IF;

  EXECUTE IMMEDIATE 'INSERT INTO ' || p_table || ' VALUES (' || p_values || ')';
  COMMIT;
END;
/

4. Use Record Types

-- Use %ROWTYPE for complete inserts
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  v_emp.employee_id := 100;
  v_emp.first_name := 'John';
  v_emp.last_name := 'Doe';
  v_emp.email := '[email protected]';
  v_emp.hire_date := SYSDATE;
  v_emp.department_id := 10;

  INSERT INTO employees VALUES v_emp;
  COMMIT;
END;
/

Common Patterns

Multiple Row Insert

-- INSERT ALL requires matching column counts
INSERT ALL
  INTO employees (employee_id, first_name, last_name) VALUES (100, 'John', 'Doe')
  INTO employees (employee_id, first_name, last_name) VALUES (101, 'Jane', 'Smith')
  INTO employees (employee_id, first_name, last_name) VALUES (102, 'Bob', 'Wilson')
SELECT * FROM dual;

UNION in INSERT…SELECT

-- All UNION branches must have same column count
INSERT INTO all_people (id, name, type)
SELECT employee_id, first_name || ' ' || last_name, 'EMPLOYEE'
FROM employees
UNION ALL
SELECT customer_id, customer_name, 'CUSTOMER'
FROM customers
UNION ALL
SELECT supplier_id, supplier_name, 'SUPPLIER'
FROM suppliers;

Related Errors

• ORA-00913 - Too many values
• ORA-00936 - Missing expression
• ORA-01400 - Cannot insert NULL
• ORA-00904 - Invalid identifier

Emergency Response

Quick Column Count Check

-- Compare column count to value count
SELECT COUNT(*) as expected_values
FROM user_tab_columns
WHERE table_name = UPPER('&table_name');

Generate INSERT Template

-- Generate INSERT template for a table
SELECT
  'INSERT INTO ' || table_name || ' (' ||
  LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id) ||
  ') VALUES (' ||
  LISTAGG(':' || column_name, ', ') WITHIN GROUP (ORDER BY column_id) ||
  ');' as insert_template
FROM user_tab_columns
WHERE table_name = UPPER('&table_name')
GROUP BY table_name;