SQL

How to Resolve ORA-00947: not enough values

ORA-00947: not enough values

ORA-00947

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 columns” 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.

Leave a Reply

Your email address will not be published. Required fields are marked *