14 Replies Latest reply on Aug 8, 2007 5:48 AM by NicloeiW

    ORA-00947: not enough values with bulk inserts,

    NicloeiW
      Hi,

      i am performing bulk insert as explained below;
      /*sample*/
      cursor c_ins is
      select a, b,c        
      from  table;

      type tt is table of c_ins%rowtype;
             t_rec tt;

      begin
          open c_ins;
          fetch c_ins bulk into t_rec;
          close c_ins;

          forall i in t_rec.first..t_rec.last
                insert into table b
                       (col1,
                         col2,
                          col3
                        ) values
                    ( treat (t_rec(i) as c_ins)).a,
                      treat (t_rec(i) as c_ins)).b, 
                       '100'
                     );

      it gives me


      Error: PL/SQL: ORA-00947: not enough values

      kindly advise me if i missed something or i am in wrong direction,

      Regards
      nic
        • 1. Re: ORA-00947: not enough values with bulk inserts,
          229023
          Aren't you having extra braces on the right side(in bold)?
          ( treat (t_rec(i) as c_ins) ).a,
                          treat (t_rec(i) as c_ins) ).b, 
                           '100'
                         );
          • 2. Re: ORA-00947: not enough values with bulk inserts,
            NicloeiW
            Yes, but after i remove that bracket, it says
            ora-00902 invalid object type

            it seems that cursor declaration is not allowed here,

            can i use record in that case ? is ther any better way to do this insert
            Message was edited by:
            Nicloei W
            • 3. Re: ORA-00947: not enough values with bulk inserts,
              JustinCave
              1) I'm assuming this isn't the real code since you've missed the COLLECT in BULK COLLECT

              2) What version of Oracle?

              3) Why wouldn't you just do
              INSERT INTO dest_table( col1, col2, col3 )
                SELECT col1, col2, '100'
                  FROM src_table
              Justin
              • 4. Re: ORA-00947: not enough values with bulk inserts,
                NicloeiW
                yes, i missed collect, this is sample of actual code what i am writing,

                some how i always get stuck up with the selective inserts with bulk collect

                i cant do simple select since this is looping activity so i want a way to do this thing ?

                if it cant be done i would restore to normal for loop
                version

                        BANNER
                1     Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
                2     PL/SQL Release 10.2.0.3.0 - Production
                3     CORE     10.2.0.3.0     Production
                4     TNS for Solaris: Version 10.2.0.3.0 - Production
                5     NLSRTL Version 10.2.0.3.0 - Production
                Message was edited by:
                Nicloei W
                • 5. Re: ORA-00947: not enough values with bulk inserts,
                  JustinCave
                  1) Note that if you want to do this and you have a reasonably large number of rows, you'd want to use BULK COLLECT with a LIMIT clause. Otherwise, you'd be using up a whole lot of PGA to store all the data at once in RAM, which very quickly gets quite unscalable.

                  2) A couple of options. You can declare the collection based on the destination table rather than the source table and populate it that way, i.e.
                  SCOTT @ jcave102 Local> create table a (col1 number, col2 number);

                  Table created.

                  Elapsed: 00:00:00.03
                  SCOTT @ jcave102 Local> create table b( col1 number, col2 number, col3 number );

                  Table created.

                  Elapsed: 00:00:00.03

                  Wrote file afiedt.buf

                    1  declare
                    2    type tbl_b is table of b%ROWTYPE;
                    3    coll_b tbl_b;
                    4    cursor c1 is select col1, col2, 100 from a;
                    5  begin
                    6    open c1;
                    7    fetch c1 bulk collect into coll_b;
                    8    close c1;
                    9    forall i in coll_b.FIRST .. coll_b.LAST
                  10      INSERT INTO b
                  11        VALUES coll_b(i);
                  12* end;
                  SCOTT @ jcave102 Local> /

                  PL/SQL procedure successfully completed.

                  Elapsed: 00:00:00.00
                  You can also replace the FORALL with a straight FOR loop
                    1  declare
                    2    type tbl_a is table of a%ROWTYPE;
                    3    coll_a tbl_a;
                    4    cursor c1 is select * from a;
                    5  begin
                    6    open c1;
                    7    fetch c1 bulk collect into coll_a;
                    8    close c1;
                    9    for i in 1 .. coll_a.count
                  10    loop
                  11      INSERT INTO b( col1, col2, col3 )
                  12        VALUES (coll_a(i).col1, coll_a(i).col2, 100);
                  13    end loop;
                  14* end;
                  SCOTT @ jcave102 Local>
                  SCOTT @ jcave102 Local> /

                  PL/SQL procedure successfully completed.

                  Elapsed: 00:00:00.01
                  3) Since you're on 10g, Oracle will automatically and transparently do a BULK COLLECT with a LIMIT of 10 (if I recall) if you are using an implicit cursor. If you opt for the straight loop approach, that means that you'd be just as efficient (and easier to code) something like
                  FOR x IN (SELECT * FROM a)
                  LOOP
                    INSERT INTO b( col1, col2, col3 )
                      VALUES( x.col1, x.col2, 100);
                  END LOOP;
                  Justin
                  • 6. Re: ORA-00947: not enough values with bulk inserts,
                    NicloeiW
                    justin understand your point,

                    but which is faster method i have some where aroud 20000 rows to process,

                    but whats use of forall and then doing for loop
                    • 7. Re: ORA-00947: not enough values with bulk inserts,
                      JustinCave
                      You probably don't want to read 20,000 rows into memory all at once. That means you'd have to use the LIMIT clause which adds a bit of complexity.

                      Explicitly doing a BULK COLLECT followed by a FOR loop (assuming you specify an appropriate LIMIT), is likely to be identical performance-wise to using an implicit cursor in 10g (not in earlier versions).

                      The first option, doing a BULK COLLECT into a collection typed to match the destination table (with an appropriate LIMIT) is likely to be the most efficient PL/SQL based solution.

                      Of course, figuring out how to move your logic into pure SQL is likely to be the most efficient of all.

                      Justin
                      • 8. Re: ORA-00947: not enough values with bulk inserts,
                        NicloeiW
                        understand justin, for ora 10g there is automatic bulk limit,

                        i only want to know how we should do this insert any work around with limit clause should be fine,

                        but again my question remain whats the way ?
                        • 9. Re: ORA-00947: not enough values with bulk inserts,
                          JustinCave
                          I'm not sure I understand... I've shown you a few different approaches and talked about the various performance implications of the various options.

                          If you're asking for an example of using the LIMIT clause to process rows in batches, here's one example.

                          Justin
                          • 10. Re: ORA-00947: not enough values with bulk inserts,
                            NicloeiW
                            thats good, i got it, actually i over looked one of your post,

                            any link where i can explore bulk inserts + limit clause ?

                            thanks again,

                            Message was edited by:
                            Nicloei W
                            • 11. Re: ORA-00947: not enough values with bulk inserts,
                              JustinCave
                              The LIMIT clause just applies to BULK COLLECT. Whether (and how) you use that is more or less independent of any bulk processing you do with that data.

                              In my example, the <<Do something with 100 records>> could be replaced with your FORALL loop (assuming, of course, that the collection type was declared in relation to the destination table).

                              Justin
                              • 12. Re: ORA-00947: not enough values with bulk inserts,
                                NicloeiW
                                thanks for this, i am clear now, as what i have to do, and have modified code as your your 2nd example, works fine, now ;-)

                                do you any links where i can explore more of batch inserts ?
                                • 13. Re: ORA-00947: not enough values with bulk inserts,
                                  JustinCave
                                  I'm not sure what you're looking for. I could point you to the PL/SQL User's Guide on http://tahiti.oracle.com, but I suspect you have a rather more specific question...

                                  Justin
                                  • 14. Re: ORA-00947: not enough values with bulk inserts,
                                    NicloeiW
                                    what i mean justin, some links in which batch inserts have been explained in with some examples,

                                    like the one you have explained,

                                    currently going through ora doc

                                    Message was edited by:
                                    Nicloei W