0 Replies Latest reply: Apr 3, 2012 5:20 AM by 926088 RSS

    ORA-24335 when using insert all

    926088
      Hi,

      i am receiving an "ORA-24335: cannot support more than 1000 columns but there are no 1000 colums" error message when performing
      a multi-row insert using INSERT ALL.

      Database: 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

      It looks like oracle stores all the values in some kind of "interims"-datastructure before executing the insert, since using duplicate values for insert, doesn't cause
      any errors.
      You can try these code samples:
      create table test_t (
       string1 varchar2(200),
       string2 varchar2(200),
       string3 varchar2(200),
       string4 varchar2(200),
       string5 varchar2(200),
       string6 varchar2(200),
       string7 varchar2(200),
       string8 varchar2(200),
       string9 varchar2(200),
       string10 varchar2(200)
      )
      /* WORKS */
      declare
       stmt     clob;     
      begin
       stmt:=' INSERT ALL ';
       for i in 1..1000 loop
         stmt:=stmt||' INTO test_t VALUES (''VALUE1''';
         for a in 2..10 loop
            stmt:=stmt||',''VALUE'||to_char(a)||'''';
         end loop;
         stmt:=stmt||') ';
       end loop;
       stmt:=stmt||' select * from dual';
       execute immediate stmt;
      end;
      /*FAILS*/
      declare
       stmt     clob;     
      begin
       stmt:=' INSERT ALL ';
       for i in 1..1000 loop
         stmt:=stmt||' INTO test_t VALUES (''VALUE1''';
         for a in 2..10 loop
            stmt:=stmt||',''VALUE'||to_char(i+a)||'''';
         end loop;
         stmt:=stmt||') ';
       end loop;
       stmt:=stmt||' select * from dual';
       execute immediate stmt;
      end;
      Does anybody have any experience with this bug and how to circumvent it or why it is showing up?

      Thank you for your help!