Resolve ORA-00947 Error Message in Oracle

Learn the cause and how to get to the bottom of the ORA-00947 error message in Oracle.

Description

When you encounter an ORA-00947 error, the following error message will appear:

ORA-00947: not enough values

Cause

You tried to execute a SQL statement that required two equal sets of values, but the 2nd set contains fewer values than the first set.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error can manifest when you are performing an INSERT and the values entered are less in variety than the columns that you are inserting into.

For example, if you tried to execute the following INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name, contact_name)
VALUES
(1000, 'Microsoft');

You would receive the following error message:

You may want to right this error by reducing the quantity of columns:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(1000, 'Microsoft');

Or by increasing the number of values to insert:

INSERT INTO suppliers
(supplier_id, supplier_name, contact_name)
VALUES
(1000, 'Microsoft', 'Bill Gates');

Option #2

This error can additionally manifest when you operate a sub-select in a WHERE clause or HAVING clause however the sub-select returns too few columns.

For example, if you tried to execute the following:

SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id
                                      FROM orders);

You would receive the following error message:

You may want to right this error by means of returning two columns in the sub-select as follows:

SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id, order_contact
                                      FROM orders);