Re: frequent commit ???

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Thu, 15 Nov 2001 12:03:43 -0800
Message-ID: <F001.003C68D0.20011115111814@fatcity.com>

Andrea,
 You didn't say how you were updateing the rows. You could create a function to do the updates that incorporates a commit, such as:

declare
i number :=0;

cursor c1 is

select  RETNBR ,                
 SALEDATE      ,         
 GAMECODE      ,         
 ORDERAMT      ,         
 CONFIRMATIONAMT,        
 ACTIVATIONAMT  ,        
 SETTLEMENTAMT  ,        
 RETURNAMT      ,        
 CASHAMT        ,        
 SALESCOMMAMT   ,        
 CASHBONUSAMT   ,        
 TOTCOMMAMT              
 from glcIDsr_curr
 WHERE SALEDATE between '06-01-2001' and '06-30-2001';                  
         
IDSR_UPDATE  C1%ROWTYPE; 
                  
begin                                    
 open c1 ;                                 
 loop                                          
fetch c1 into IDSR_UPDATE ;                 
 exit when c1%NOTFOUND  ;                 
 insert into GLCIDSR_HIST01          
 values(IDSR_UPDATE.RETNBR,                 
 IDSR_UPDATE.SALEDATE,               
 IDSR_UPDATE.GAMECODE,               
 IDSR_UPDATE.ORDERAMT,               
 IDSR_UPDATE.CONFIRMATIONAMT,        
 IDSR_UPDATE.ACTIVATIONAMT,          
 IDSR_UPDATE.SETTLEMENTAMT,          
 IDSR_UPDATE.RETURNAMT,              
 IDSR_UPDATE.CASHAMT,                
 IDSR_UPDATE.SALESCOMMAMT,           
 IDSR_UPDATE.CASHBONUSAMT,           

 IDSR_UPDATE.TOTCOMMAMT);
i := i + 1;
if i > 200000

   then

     commit; 
     i := 0;

   end if;
end loop;
commit;
close c1;
end;

ROR mª¿ªm

>>> andreaoracle_at_yahoo.com 11/15/01 01:30PM >>>
Hi all,

I'm updating 1 million row, is it a way to do a commit after every 1000 rows? Thank you!

Andrea

---

Do You Yahoo!?
Find the one for you at Yahoo! Personals http://personals.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Andrea Oracle
  INET: andreaoracle_at_yahoo.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author: Ron Rogers
  INET: RROGERS_at_galottery.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Thu Nov 15 2001 - 14:03:43 CST