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 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.
this diagnosis is correct – but sometimes we also get “not enough values” if the SQL insert statement has a single line with more than 2000 / 3000 characters /allphabets.
i.e we have a table with 68 columns in production and test db.
when we do :
to copy a row from production to test —
insert into test..a
select * from production.a
we get the error “not enough values” ora 947. BUT when compared – the no /type of columns were correct.
So – used SQL Developer ” insert /*insert*/ * into test.a ( select * from production.a ) ” to generate the INSERT statement — break it into 2 or more lines — then apply the same – it works.
IS there a better way to do this.
Thanks in advance for any soltuions/workarounds.
Interesting! Maybe I should find out sometime. Anyway, thanks for your feedback!