Oracle/PLSQL: ORA-00947 Error

Error:

ORA-00947: not enough values

Cause:

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

Action:

The options to resolve this Oracle error are:

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');

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);