Commiting limited no of rows at a time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Commiting limited no of rows at a time

  1. #1
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    I am Selecting number of rows from a table and inserting into another table in a pl/sql block.I want to commit only limited number of rows at a time(say 2000).how can this be achieved.

    As always Thanks!

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    create or replace procedure myproc as
    x number(4);
    begin
    loop
    insert into newtable values( . . . );
    x := x+1;
    if mod(x,2000) = 0 then
    ___commit;
    end if;
    exit when . . .
    end loop;
    end;
    /

  3. #3
    Join Date
    Apr 2001
    Posts
    12
    create or replace procedure test
    x number;
    begin
    insert into testtable......
    select count(*) into x from testtable;
    if mod(x,2000)=0 then commit;
    end if;
    end;

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by arun_rashmi
    select count(*) into x from testtable;
    You *gotta* be kidding

    Stick with Kmesser's solution!

    Although I'd add that you should look into bulk processing (FORALL) which will really save you time in the loop.

    - Chris

  5. #5
    Join Date
    Jun 2000
    Location
    Memphis,TN
    Posts
    30
    Thanks Guys! It worked.

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Originally posted by arun_rashmi
    select count(*) into x from testtable;
    You *gotta* be kidding
    I think quitting smoking is giving you a little "jmodic" sense of humor...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    I *am* becoming a real PITA, aren't I?

    I've either gotta quit this book ar take up smoking again - something's gotta give

    - Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width