From: jstrange@imtn.dsccc.com (John Strange)
Subject: Re: Help! failed to extend rollback segment error.
Date: 1995/11/11
Message-ID: <482cdf$32r@tpd.dsccc.com>#1/1
distribution: world
references: <480ato$kc9@bcarh8ab.bnr.ca> <480naa$aol@srvr1.engin.umich.edu>
organization: DSC Communications Corporation, Plano, Texas USA
newsgroups: comp.databases.oracle


	My cursor is better than your cursor :)

	My commit did not kill my cursor.

BEGIN
  Declare
        row_cnt number := 0 ;
        row_id rowid ;
 
        cursor srlexp is
          select rowid from srlexp_sli
           where pk_product = 'OCC' ;
  begin
        open srlexp ;
 
        fetch srlexp into row_id ;
 
        while (srlexp%FOUND) loop
          row_cnt := row_cnt + 1 ;
 
          update srlexp_sli sli
            set sli.seqnum = (select seqnum
                                from srlexp_pkg
                               where pk_product = sli.pk_product
                                 and pk_rec_key = sli.pk_key
                             )
          where rowid = row_id ;
 
          if mod (row_cnt, 50000) = 0 then
            commit ;
          end if ;
 
          fetch srlexp into row_id ;
        end loop ;
 
 
        commit ;        /* any remaining rows   */
        close srlexp ;
  end ;
END ;
/
 

Ed Steinman (esteinma@umich.edu) wrote:
:> If possible do commits  in the middle of your job so that there's not so
:> many records to post. You might be abe to accomplish this by using a loop
:> in a PL/SQL script. This idea won't work if you're using cursors
:> however--a commit kills a cursor.
:> ------------------------------------------------------------------
:> Ed Steinman                            What the Zen Buddhist said to the
:> Computing Unit, School of Dentistry       hot dog vendor:
:> University of Michigan
:> Ann Arbor, MI 48109-1078                    "Make me one with everything."
:> 313-764-9850
:> FAX 313-747-4024
:> e-mail: esteinma@umich.edu

--
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.