ORA-00947: not enough values error occurs when fewer column values are entered in the VALUES or SELECT clause than are required in the insert statement. If you attempt to run an insert statement with fewer values in the VALUES or SELECT clauses, the insert statement will fail to insert values into the table. The insert command was unable to identify values for all columns.
The ORA-00947: not enough values error occurs in the select subqueries. If the number of columns returned by the subquery is less 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 less items than the first set, then the oracle error happens. If the subquery returns less column values in the WHERE or HAVING clause, the outer query will fail to process.
How this ORA-00947 error occur
The error occurs if the values or select clause returns less columns than the necessary columns in the insert statement. Also, if the subquery produces less column values than the main query requires, the error will occur. In the example below the employee table contains three columns id, name and salary. The insert statement value clause contains two values. These two values could not insert into the table that requires three column values.
create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp (id,name) values(1,'emp1');Error
Error starting at line : 13 in command -
insert into emp (id,name) values(1,'emp1');
Error at Command Line : 13 Column : 13
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"Root Cause
If the number of column values given in the insert statement is less than the number of columns in the table, this error occurs. The number of column values in the VALUES / SELECT clause should be more than the number of required column values. The insert statement was unable to find a value for one or more column values. As a result, the error occurs.
Solution 1
If the number of column values is less than expected, add the extra column values. The number of columns in the table should be the same as the number of values in the VALUES clause. If the insert statement lacks sufficient value, it should be added. If the table has any extra columns that will not be needed in the future, the table column may be deleted.
Problem
create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp (id,name) values(1,'emp1');
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"Solution
insert into emp (id,name) values(1,'emp1',1000);
1 row inserted.create table emp (
id int,
name varchar2(100)
);
insert into EMP values(1,'emp1');
1 row inserted.Solution 2
If the insert statement inserts values into a subset of the columns, the number of column values should equal the number of columns provided in the insert statement. Additional column values should be added if the number of column values is fewer than the number of columns. If any of the specified columns is not required, it should be deleted. This will resolve the error.
Problem
create table emp (
id int,
name varchar2(100),
salary int
);
insert into emp (id,name,salary) values(1,'emp1');
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough 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
If there are any syntax errors in the insert statement, this might be the cause of the issue. The string and date values will contain single quotes; incorrect usage of single quotes may lead to errors. Check the insert statement. If there is a syntax error, it must be corrected.
Problem
create table emp (
id int,
name varchar2(100),
dept varchar2(100)
);
insert into emp (id,name,dept) values(1,'emp1,sales');
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"Solution
insert into emp (id,name,dept) values(1,'emp1','sales');
1 row inserted.Solution 4
If a select statement returns less values than the insert statement, the insert statement cannot add the data to the table. The returned set of the select statement must match the input columns. The returning columns should be added to the select statement, and the columns that are no longer needed should be deleted from the insert statement.
Problem
create table emp (
id int,
name varchar2(100),
salary int
);
create table manager (
id int,
name varchar2(100)
);
insert into emp as select * from manager;
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"Solution
insert into emp(id, name) as select * from manager;
1 row inserted.
insert into emp(id, name) as select id, name from manager;
1 row inserted.create table manager (
id int,
name varchar2(100),
salary int
);
insert into emp as select * from manager;
1 row inserted.Solution 5
The returned set in the subqueries should match the main query in the select subqueries. This error can also occur if your subquery in the WHERE clause produces less columns values. If the subquery returns less columns from the table, you must change it to return required columns.
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, name) in (select id from manager);
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"Solution
select * from emp where (id, name) in (select id, name from manager);