Oracle / PLSQL: ORA-00947 Error Message

Learn the cause and how to resolve 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 second set contains fewer values than the first set.

Resolution

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

Option #1

This error can occur when you are performing an INSERT and the values entered are less in number 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 could correct this error by reducing the number 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 also occur when you perform a sub-select in a WHERE clause or HAVING clause but 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 could correct this error by 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);