Resolve ORA-00913 Error Message in Oracle

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

Description

When you come across 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, however you entered more gadgets in the 2d set than was in the first set.

Resolution

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

Option #1

This error frequently takes place when you are performing a INSERT statement and enter more values in the VALUES clause than the variety of columns that you listed.

For example, if you done the following INSERT statement:

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

In this example, you have chosen to insert values into two columns (supplier_id and supplier_name), however you have entered three values (1000, Microsoft, and Bill Gates).

You need to adjust your INSERT announcement so there are the equal variety 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 achieved 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 merchandise table. You need to alter 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%);