Learn the cause and how to resolve the ORA-00947 error message in Oracle.
Description
When you encounter an ORA-00947 error, the following error message will appear:
- ORA-00947: not enough values
Cause
You tried to execute a SQL statement that required two equal sets of values, but the second set contains fewer values than the first set.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
This error can occur when you are performing an INSERT and the values entered are less in number than the columns that you are inserting into.
For example, if you tried to execute the following INSERT statement:
INSERT INTO suppliers (supplier_id, supplier_name, contact_name) VALUES (1000, 'Microsoft');
You would receive the following error message:
You could correct this error by reducing the number of columns:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'Microsoft');
Or by increasing the number of values to insert:
INSERT INTO suppliers (supplier_id, supplier_name, contact_name) VALUES (1000, 'Microsoft', 'Bill Gates');
Option #2
This error can also occur when you perform a sub-select in a WHERE clause or HAVING clause but the sub-select returns too few columns.
For example, if you tried to execute the following:
SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id
FROM orders);
You would receive the following error message:
You could correct this error by returning two columns in the sub-select as follows:
SELECT *
FROM suppliers
WHERE (supplier_id, contact_name) IN (SELECT supplier_id, order_contact
FROM orders);
ORA-00947: недостаточно значений
Причина:
Ваше SQL выражение требует два набора величин эквивалентных по количеству, но второй набор содержит меньше элементов, чем первый. Это может случиться в WHERE и HAVING предложении в котором вложенный SELECT возвращает мало колонок так как в:
WHERE (A,B) IN (SELECT C FORM …)
Другой общий случай этой ошибки в операторе INSERT в котором VALUES или SELECT предложение не содержит достаточно величин необходимых для INSERT как в:
INSERT INTO (EMPNO, ENAME) VALUES (‘JONES’)
Действие:
Проверьте число элементов в каждом наборе и измените SQL оператор для эквивалентности.
With respect to ORA-00913: too many values, column list mismatching can result another type of error, called ORA-00947: not enough values.
Let’s see how we reproduce the error. First of all, we create an empty table employees_2 from employees.
SQL> create table employees_2 as select * from employees where 1 = 2;
Table created.
Now we add a column gender to the new table.
SQL> alter table employees_2 add gender char(1) default 'M' not null;
Table altered.
We try to use INSERT SELECT to fill some data to the new table.
SQL> insert into employees_2 select * from employees;
insert into employees_2 select * from employees
*
ERROR at line 1:
ORA-00947: not enough values
ORA-00947 was thrown because of column list mismatch. Which «not enough values» really means «not enough column values» in my perception. You have to compare the definitions between source and target tables.
Another contrary type of error, ORA-00913: too many values means the number of returned columns in the inner query is more than required number of columns in the outer statement.
Learning Oracle SQL. I have created a table that looks like this:
CREATE TABLE Kocury
(imie VARCHAR2(15) NOT NULL,
plec VARCHAR2(1) CONSTRAINT allowedValues CHECK (plec in ('M', 'D')),
pseudo VARCHAR2(15) Constraint PK_KOCURY PRIMARY KEY,
funkcja VARCHAR2(10) CONSTRAINT fk_checkF REFERENCES Funkcje (funkcja),
szef VARCHAR2(15) CONSTRAINT fk_checkS references Kocury (pseudo),
w_stadku_od DATE default sysdate,
przydzial_myszy NUMBER(3),
myszy_extra NUMBER(3),
nr_bandy NUMBER(2) CONSTRAINT fk_checkN REFERENCES Bandy(nr_bandy)
);`
and then I tried to insert some data:
INSERT INTO Kocury(imie, plec, pseudo, funkcja, szef, w_stadku_od, przydzial_myszy ,myszy_extra, nr_bandy)
VALUES ('JACEK', 'M', 'PLACEK', 'LOWCZY', 'LYSY', '2008-12-01, 67',NULL , 2);
as far as I am concerned the data types all match. But in Oracle SQL Developer I get this:
Error starting at line : 41 in command -
INSERT INTO Kocury(imie, plec, pseudo, funkcja, szef, w_stadku_od, przydzial_myszy ,myszy_extra, nr_bandy)
VALUES ('JACEK', 'M', 'PLACEK', 'LOWCZY', 'LYSY', '2008-12-01, 67',NULL , 2)
Error at Command Line : 42 Column : 1
Error report -
SQL Error: ORA-00947: not enough values
00947. 00000 - "not enough values"
*Cause:
*Action:
I am not sure what’s happening and how to get my data inserted. What could I be doing wrong? These topics didn’t help me:
ORA-00947 : Not Enough Values
ORA-00947: not enough values
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);