Request PL/SQL Help

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: 28 Jun 2001 13:44:03 -0700
Message-ID: <f98999c8.0106281244.2e88f73b@posting.google.com>

I am trying to convert some data that previously had a non-unique index, and now a unique index is required on emplid,start_dt,sequence_nbr.

I want to increment sequence_nbr (they're all 1) so I can create the unique index. My PL/SQL is a little rusty. Could someone take a look at what I've done and get me straightened out?  

I'd be grateful.

declare
  hold_emp varchar2(11),
  seq_num integer(3),
  cursor c1 is SELECT

               EMPLID


               ,START_DT

               ,SEQUENCE_NBR

               ,COUNT(*)

               FROM PS_PRIORWORK_EXPER
               GROUP BY 
                EMPLID


               ,START_DT

               ,SEQUENCE_NBR

               HAVING COUNT(*) > 1;

begin
  for i in c1 loop
    if i.emplid = hold_emp then

       seq_num = seq_num + 1;
       dbms_sql(update ps_priorwork_exper up set sequence_nbr :=

seq_num where up.emplid = c1.emplid and up.start_dt = c1.start_dt);

    else

        seq_num :=0;
    end if;
    hold_emp := c1.emplid;
  end loop;
end; Received on Thu Jun 28 2001 - 15:44:03 CDT