This compiled well until I got to the 129-th into. When adding the 130-th I got the error "too many values".
Why so? I added two more tables in another forall insert and it compiled OK
I can't figure out what limit I've reached.
Huh? Sure you can figure out what 'limit' you reached. You just posted it!
See the SQL Language doc for the INSERT statement
conditional_insert_clauseto perform a conditional multitable insert. Oracle Database filters each
insert_into_clausethrough the corresponding
WHENcondition, which determines whether that
insert_into_clauseis executed. Each expression in the
WHENcondition must refer to columns returned by the select list of the subquery. A single multitable insert statement can contain up to 127
See that last sentence? The limit is 127.
That's right. This is what I've "won at the lottery".
Doesn't please me at all, but have to go on with it. And I have the means.
Still, at the moment I was not sure what the limits were. I found out how.
Just think this replaces a function called on N columns in a merge statement, the function having an insert in it!!!
Re-design would seem appropriate, but...
As for the limits of multi-insert, I have performed some tests.
create table zzz (
select 'when 1=1 then into zzz (c10,c2,c3,c4,c5,c6,c7,c8,c9,c1) values (a2,a3,a4,a5,a6,a7,a8,a8,a10,'||level||') '
connect by level<=190
-- select 2 a2, 3 a3, 4 a4, 5 a5, 6 a6, 7 a7, 8 a8, 9 a9, 10 a10 from dual
And the generated SQL works with 127*10=1270 columns but says “too many values” for 128*10 columns.
Thus I found the answer to what I was asking.