Re: Help! failed to extend rollback segment error.

        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 ;

Ed Steinman (esteinma_at_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_at_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.