ORA-00913: too many values

Cause:

Error itself says there are too many values(columns) in the table. For example, if your table has 5 columns and you try to use IAS(insert as select) from a table containing 10 columns.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(10)
 NUM                                                NUMBER(10)

SQL> desc dba_source;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(128)
 NAME                                               VARCHAR2(128)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)
 ORIGIN_CON_ID                                      NUMBER

In above output,both tables got different mismatching columns

SQL> insert into t select * from dba_source;
insert into t select * from dba_source
            *
ERROR at line 1:
ORA-00913: too many values

Workaround:

Create a new table and insert the records into it or add columns and insert data

SQL> create table t1 as select * from dba_source;

Table created.


SQL> insert into t1 select * from dba_source;

326092 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