SQL Error: ORA-00913: too many values issue appears when you enter more column values in the VALUES / SELECT clauses than the number of columns required in the insert statement. The values in the VALUES clause are more than what is needed by the insert statement. If you try to run an insert statement with too many values in the VALUES / SELECT clause, the insert statement will fail to insert the values into the table. Oracle throws the ORA-00913: too many values error in this scenario.
The SQL Error: ORA-00913: too many values error occurs in the select subqueries. If the number of columns returned by the subquery is more than the number of columns required by the outer query, the outer query will be unable to handle the data returned by the inner query. Oracle throws the error in this scenario.
The SQL statement such as insert and select requires two sets of values that are equal in number. The datatype and order of the column should be same in each sets. The second set will be in VALUES / SELECT clause. If the second set contains more items than the first set, then the oracle error SQL Error: ORA-00913: too many values occurs. If the subquery returns too many column values in the WHERE or HAVING clause, the outer query will fail to process.
When this ORA-00913 Error occur
The error SQL Error: ORA-00913: too many values occurs if the values or select clause returns more columns than the necessary columns in the insert statement. Also, if the subquery produces more column values than the main query requires, the error will occur. In the example below the employee table contains two columns id and name. The insert statement value clause contains three values. These three values could not insert into the table that contains only two columns.
create table emp (
id int,
name varchar2(100)
);
insert into EMP values(1,'emp1',1000);Error
Error starting at line : 12 in command -
insert into EMP values(1,'emp1',1000)
Error at Command Line : 12 Column : 13
Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"Root Cause
This SQL Error: ORA-00913: too many values error occurs if the number of columns returned in the values or select clause exceeds the number of columns required in the insert statement. The insert statement was unable to find the extra column in the table, or the extra column is irrelevant to the table.
Solution 1
Remove the additional column in the values clause if the extra column value is provided in the insert statement. If the table needs an additional column, modify the table and add the required column. The number of column values in the values clause should be the same as the number of columns in the table. Check the table’s columns and make changes.
Problem
create table emp (
id int,
name varchar2(100)
);
insert into EMP values(1,'emp1',1000);
Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"Solution
insert into EMP values(1,'emp1');
1 row inserted.create table emp (
id int,
name varchar2(100),
salary int
);
insert into EMP values(1,'emp1',1000);
1 row inserted.Solution 2
The insert statement requires two sets of values equal in number. This SQL Error: ORA-00913: too many values occurs when the second set contains more items than the first set. The error will occur if the number of columns in the values column exceeds the number of inserted columns. The numbers of columns in the values clause should be matched by the inserted columns.
Problem
create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp (id,name) values(1,'emp1',1000);
Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"Solution
insert into emp (id,name,salary) values(1,'emp1',1000);
1 row inserted.insert into emp (id,name) values(1,'emp1');
1 row inserted.Solution 3
In the insert statement, if a select statement returns too many values, the insert statement cannot add the data to the table. The select statement’s returning set must match the entered columns. The returned columns should be deleted in the select statement, or the needed columns should be specified in the insert statement.
Problem
create table emp (
id int,
name varchar2(100)
);
create table manager (
id int,
name varchar2(100),
salary int
);
insert into emp as select * from manager;
Error report -
SQL Error: ORA-00913: too many values
00913. 00000 - "too many values"Solution
insert into emp as select id, name from manager;
1 row inserted.
insert into emp(id, name) as select id, name from manager;
1 row inserted.create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp as select * from manager;
1 row inserted.Solution 4
The returned set in the subqueries should match the main query in the select subqueries. This error SQL Error: ORA-00913: too many values can also occur if your subquery in the WHERE clause produces too many columns values. If the subquery returns all columns from the table, you must change it to return only one column.
Problem
create table emp (
id int,
name varchar2(100),
salary int
);
create table manager (
id int,
name varchar2(100),
salary int
);
select * from emp where id in (select * from manager);
ORA-00913: too many values
00913. 00000 - "too many values"Solution
The subquery returns all column values from the manager table. In the main query where clause requires only id values. As the subquery returns too many values, the error occurs. In the query below returns only id column from the manager table. where clause matches id column value from manager table to id column value from employee table.
select * from emp where id in (select id from manager);