Re: Commits in a middle of a transaction

From: John Strange <jstrange_at_imtn.dsccc.com>
Date: 1997/05/23
Message-ID: <5m40hj$853@camelot.dsccc.com>#1/1

create a plsql block, declare your cursor, count each row, commit when ever you like. Example:

BEGIN
  Declare

        row_cnt number := 0 ;
        s_num   number (15) ;
        i_num   number (6) ;
        c_date  date ;
        c_by    number (15) ;
        l_date  date ;
        l_by    number (15) ;
        i_ver   number (8) ;
 
        cursor srlexp is
          select sli.seqnum, sli.inum,
          sl.creation_date, sl.created_by,
          sl.last_update_date, sl.last_updated_by,
          sli.ext
          from a_eng_srl.srlexp_sli sli, crt_software_loads sl
          where sli.seqnum = sl.sw_load_num ;
 

 begin
        open srlexp ;
 
        fetch srlexp into s_num, i_num, c_date, c_by, l_date, l_by, i_ver ;
 
        while (srlexp%FOUND) loop
          row_cnt := row_cnt + 1 ;
 
          insert into crt_sw_load_items
            (sw_load_num, item_name_num,
             creation_date, created_by,
             last_update_date, last_updated_by,
             item_version
            )
            values (s_num, i_num, c_date, c_by, l_date, l_by, i_ver) ;
 
          if mod (row_cnt, 10000) = 0 then
            commit ;
          end if ;
 
          fetch srlexp into s_num, i_num, c_date, c_by, l_date, l_by, i_ver ;
        end loop ;
 
        commit ;        /* any remaining rows   */
        close srlexp ;

  end ;
END ;
/
exit;

Kris Rahl (krahl_at_evolving.com) wrote:
: Does anyone know if there is a way to commit partway through the
: completion of a transaction? We have a 20,000 row insert and was
: wondering if a commit can be forced easily before it's completed.
 

: Thanks,
: Kris Rahl
: Krahl_at_evolving.com

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.

Received on Fri May 23 1997 - 00:00:00 CDT