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