Is there a limit to ORACLE's Multi Insert?

Question

INSERT ALL
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
.......
INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
SELECT * FROM DUAL;

I'm using this statement to insert multiple rows into a table.

Sometimes I inserts correctly, even if I give it a couple of thousands of lines. I even inserted 10100 lines with it!

But sometimes, it says this

ORA-24335: cannot support more than 1000 columns

Any ideas ?

Solution

Yes, there is a limit:

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm

Restrictions on Multitable Inserts
Multitable inserts are subject to the following restrictions:
...
- In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.
...

You're inserting in a single table, so it can be done without multi table insert. For example, by using UNION ALL:

insert into t (col1, col2, col3)
select 'val1_1', 'val1_2', 'val1_3' from dual
union all
select 'val1_1', 'val1_2', 'val1_3' from dual
...

Comments

[yktoo]

When inserting into a single table the limit does not seem to apply. At least not in 11g, I managed to insert 10K columns in total, without issue.

[Abhishek Singh]

Post this as a seperate question