5 Replies Latest reply: Jul 24, 2014 5:00 PM by rp0428 RSS

    Forall insert all tricky thing

    BEDE

      I have a rather large piece of code that is something like below:

       

      forall i in indices of r_cs

      insert all

      when ... -- conditions referring to r_cs

      then into t (...)

        values (...) -- including elements of r_cs

      ...

       

      and so on.

      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.

      So far I have 645 columns in the insert all. And I have 645 components of the r_cs table referred.

        • 1. Re: Forall insert all tricky thing
          Gaurav_91

          It looks like you are trying to insert more values as compare to columns.

          • 2. Re: Forall insert all tricky thing
            Billy~Verreynne

            You have a serious design and coding problem if you need to insert 645 columns using a FORALL bulk bind....

             

            How on earth is that maintainable? Never mind logical?

            • 3. Re: Forall insert all tricky thing
              rp0428
              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

              http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm

              conditional_insert_clause

               

              Specify the conditional_insert_clause to perform a conditional multitable insert. Oracle Database filters each insert_into_clause through the corresponding WHEN condition, which determines whether that insert_into_clause is executed. Each expression in the WHEN condition must refer to columns returned by the select list of the subquery. A single multitable insert statement can contain up to 127 WHEN clauses.

              See that last sentence? The limit is 127.

              • 4. Re: Forall insert all tricky thing
                BEDE

                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 (

                c1 varchar2(1000),

                c2 varchar2(1000),

                c3 varchar2(1000),

                c4 varchar2(1000),

                c5 varchar2(1000),

                c6 varchar2(1000),

                c7 varchar2(1000),

                c8 varchar2(1000),

                c9 varchar2(1000),

                c10 varchar2(1000)

                )

                ;

                 

                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||') '

                from dual

                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.

                 

                • 5. Re: Forall insert all tricky thing
                  rp0428

                  Still, at the moment I was not sure what the limits were. I found out how.

                   

                  Yes - you 'found out' by reading my reply where I quoted that limit from the Oracle documentation.