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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s