How to Fix ORA-00913: Too Many Values in Oracle

ORA-00913 means a statement supplies more values than there are columns to receive them. It shows up in INSERT statements whose VALUES list or SELECT is longer than the column list, and in comparisons where a subquery returns more columns than the left side has. Count both sides: they must match.

Reproduce the Error

Example:

create table demo_staff2 (staff_id number, name varchar2(30));
insert into demo_staff2 values (1, 'Mia', 'Sales');
insert into demo_staff2 (staff_id, name) select 1, 'Mia', 'Sales' from dual;
select * from dual where (dummy) = (select 'X', 'Y' from dual);

Output:

Table DEMO_STAFF2 created.

Error starting at line : 2
In command -
insert into demo_staff2 values (1, 'Mia', 'Sales')
Error at Command Line : 2 Column : 13
Error report -
SQL Error: ORA-00913: too many values

Error starting at line : 3
In command -
insert into demo_staff2 (staff_id, name) select 1, 'Mia', 'Sales' from dual
Error at Command Line : 3 Column : 13
Error report -
SQL Error: ORA-00913: too many values

Error starting at line : 4
In command -
select * from dual where (dummy) = (select 'X', 'Y' from dual)
Error at Command Line : 4 Column : 37
Error report -
SQL Error: ORA-00913: too many values

• The table has two columns, and the INSERT gives three values.
• The column list names two columns, and the SELECT returns three.
• The comparison has one column on the left and a subquery with two on the right.

Fix: Match the Counts

Either the table needs the extra column, or the statement needs fewer values:

Example:

alter table demo_staff2 add department varchar2(20);
insert into demo_staff2 values (1, 'Mia', 'Sales');                         -- now three columns
insert into demo_staff2 (staff_id, name) select 2, 'Leo' from dual;           -- or fewer values
select * from demo_staff2 order by staff_id;

Output:

Table DEMO_STAFF2 altered.

1 row inserted.

1 row inserted.

   STAFF_ID NAME    DEPARTMENT
___________ _______ _____________
          1 Mia     Sales
          2 Leo

Things to Know

• An INSERT without a column list counts all columns of the table, invisible columns excluded; list the columns to be safe.
• The opposite mistake, fewer values than columns, raises ORA-00947: not enough values.
• In PL/SQL, SELECT ... INTO with more select-list items than variables raises ORA-00913 at compile time.

Related Guides

• How to Fix ORA-00947: Not Enough Values in Oracle
• How to Use Invisible Columns in Oracle

Conclusion

ORA-00913 means more values than columns. Count the items on both sides of the INSERT or comparison, then remove the extra values or add the missing column.