RE: Committing after every 100 deletes/updates

From: John Blake <jblake_at_arrow.com>
Date: Fri, 25 Apr 2003 14:32:05 -0800
Message-ID: <F001.00589F77.20030425143205@fatcity.com>

Assuming you have declared a cursor and are looping through it to control processing.
you need to

declare
m_counter number :=0;
m_commit number := 100;
cursor x is select .......;

begin
  for currec in x loop
    if updateing

     then
      m_counter := m_counter+1;
      update
      if m_counter = m_commit
        then
          commit;
          m_counter := 0;
      end if;

    end if;
  end loop;
  commit;
end;

-----Original Message-----
Baswannappa, Shiva
Sent: Friday, April 25, 2003 5:13 PM
To: Multiple recipients of list ORACLE-L

Hi Gurus

Another TGIF question.

I am using 9iV2 on HP-UX R-11. my question is:

Is there a way to auto commit changes made to a table after every say 100 records or so? I am processing 1000s of records and want to make sure the changes are autocommitted. the changes are made in a procedure bundled in a package with several other procedures.

I thought I saw a statement similar to "delete from tab1 where col1='col1'.... commit

not sure

or may be definition of the table can be changed in such a way to commit periodically!!!

Thanks in advance for all the suggestion I get

Regards

Shiva

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Baswannappa, Shiva
  INET: SXBaswan_at_dcss.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Blake
  INET: jblake_at_arrow.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Fri Apr 25 2003 - 17:32:05 CDT