Oracle / PLSQL: ORA-00913 Error Message

Learn the cause and how to resolve the ORA-00913 error message in Oracle.

Description

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

• ORA-00913: too many values

Cause

You tried to execute a SQL statement that required two sets of equal values, but you entered more items in the second set than was in the first set.

Resolution

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

Option #1

This error often occurs when you are performing a INSERT statement and enter more values in the VALUES clause than the number of columns that you listed.

For example, if you executed the following INSERT statement:

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

In this example, you've chosen to insert values into 2 columns (supplier_id and supplier_name), but you've entered 3 values (1000, Microsoft, and Bill Gates).

You need to modify your INSERT statement so there are the same number of columns as there are values. For example:

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

Option #2

This error can also occur when your subquery in the WHERE clause returns too many columns.

For example, if you executed the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_id > 5000
AND supplier_id IN (SELECT * FROM products
                    WHERE product_name LIKE 'H%);

In this example, the subquery returns all columns from the products table. You need to modify the subquery to return only one column as follows:

SELECT *
FROM suppliers
WHERE supplier_id > 5000
AND supplier_id IN (SELECT product_id FROM products
                    WHERE product_name LIKE 'H%);