How to Auto generate Primary Key in existing data

 

Note : My PK Column LINE_NO.

Step : 1
=====

Create Sequence SEQ_DEPT_2

Step : 2
=====

CREATE OR REPLACE PROCEDURE CREATE_PK_LINE_NO (
   p_vrno               OUT    VARCHAR2)

IS

   v_vrno            NUMBER;
 BEGIN

    SELECT  SEQ_DEPT_2.NEXTVAL

    INTO v_vrno

    FROM  dual;

    p_vrno := v_vrno;

COMMIT;

END;

/

Step : 3
=====

DECLARE

 CURSOR C1 IS

  select * from DEPT_2

   where LINE_NO IS NULL

  order by DEPTNO_2  ;

  

  v_vrno NUMBER;

  

BEGIN

 FOR R1 IN C1 LOOP

  CREATE_PK_LINE_NO (v_vrno);

  

  UPDATE DEPT_2

  SET LINE_NO = v_vrno

  WHERE DEPTNO_2||DNAME_2||LOC_2||DEPTNO_1 = R1.DEPTNO_2||R1.DNAME_2||R1.LOC_2||R1.DEPTNO_1

  AND LINE_NO IS NULL

   ;

 END LOOP;

 COMMIT;

END;