ORA-00947: not enough values
Cause:
This error occur, if we there is mismatch in the column values between source and destination using IAS (insert as select)
SQL> insert into test.dbo select * from dba_source;
insert into test.dbo select * from dba_source
*
ERROR at line 1:
ORA-00947: not enough values
Workaround:
Create a test table using dba_objects
SQL> create table test.dbo nologging as select /*+ parallel(4) */ * from sys.dba_objects;
Table created.
Insert rows using dba_source
SQL> insert into test.dbo select * from dba_source;
insert into test.dbo select * from dba_source
*
ERROR at line 1:
ORA-00947: not enough values
Due to mismatch in the number of columns between two tables, we get the error
SQL> desc dba_source;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)
SQL> desc dba_objects;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
Insert the rows with the same number of column value with same datatype in the table
SQL> insert into test.dbo select /*+parallel(4)*/ * from dba_objects;
86649 rows created.
SQL> commit;
Commit complete.