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);
No comments:
Post a Comment