Re: Request PL/SQL Help

Buck Turgidson wrote:

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

Drop the dbms_sql and use the following:

execute immediate <sql string>;

and the code to execute must be in the form of a string which you can accomplish by concatenating your text with your variables using vertical bars (||).

Daniel A. Morgan Received on Thu Jun 28 2001 - 16:12:06 CDT