Reset Sequence Value

I have old table already have a lot of data, I take decision to create new sequence to use it for getting serials in primary key of table to it.

For example : table SCOTT.EMP has EMPNO primary key and I want to create new sequence EMPNO_SEQ to store NEXTVAL of sequence in EMPNO column.

CREATE SEQUENCE SCOTT.EMPNO_SEQ
   START WITH 1
   MAXVALUE 9999999
   MINVALUE 0
   CACHE 25;

Oooooops, EMPNO column already has stored data that is maximum than sequence next value.
So I take decision to create generic procedure to reset sequence next value to maximum value of primary key in any table.

I created RESET_SEQUENCE procedure which takes three parameters
a-IN_SEQUENCE_NAME : name of sequence that I will use.
b-IN_TABLE_NAME : name of table which I will store sequence next value in its column
c-IN_COLUMN_NAME : name of column, If it is null I will get column which is primary key



Procedure RESET_SEQUENCE Script

 CREATE OR REPLACE PROCEDURE RESET_SEQUENCE (  
   IN_SEQUENCE_NAME  VARCHAR2,  
   IN_TABLE_NAME    VARCHAR2,  
   IN_COLUMN_NAME   VARCHAR2 DEFAULT NULL)  
 IS  
   LREC$SEQUENCE_REC  USER_SEQUENCES%ROWTYPE;  
   LN$GAP       NUMBER := 0;  
   LN$NEXT_VALUE    NUMBER := 0;  
   LN$MAX_VALUE    NUMBER := 0;  
   LC$PK_COLUMN    VARCHAR2 (30);  
   LN$DUMP       NUMBER := 0;  
 BEGIN  
   IF IN_COLUMN_NAME IS NULL  
   THEN  
    -- if user doesn't enter column name in parameter  
    -- then get column which is primary key  
    BEGIN  
      SELECT COLUMN_NAME  
       INTO LC$PK_COLUMN  
       FROM USER_CONS_COLUMNS  
      WHERE CONSTRAINT_NAME =  
           (SELECT CONSTRAINT_NAME  
            FROM USER_CONSTRAINTS  
            WHERE TABLE_NAME = IN_TABLE_NAME  
               AND CONSTRAINT_TYPE = 'P');  
    EXCEPTION  
      WHEN NO_DATA_FOUND  
      THEN  
       --Oooooooooops, no primary key in table  
       RAISE_APPLICATION_ERROR (  
         -20000,  
         'Table ' || IN_TABLE_NAME || ' hasn''t Primary Key');  
      WHEN TOO_MANY_ROWS  
      THEN  
       --Ooooops , Table contains composite primary key  
       RAISE_APPLICATION_ERROR (  
         -20000,  
         'Table ' || IN_TABLE_NAME  
         || ' has composite Primary Key, So you must identify column based on sequence');  
    END;  
   ELSE  
    -- user enter column name as parameter  
    LC$PK_COLUMN := IN_COLUMN_NAME;  
   END IF;  
   --get next value in sequence  
   EXECUTE IMMEDIATE 'SELECT ' || IN_SEQUENCE_NAME || '.NEXTVAL FROM DUAL'  
    INTO LN$NEXT_VALUE;  
   --get max value of column  
   EXECUTE IMMEDIATE  
    'SELECT MAX(' || LC$PK_COLUMN || ') FROM ' || IN_TABLE_NAME  
    INTO LN$MAX_VALUE;  
   IF (LN$NEXT_VALUE <= LN$MAX_VALUE)  
   THEN  
    --store sequence old data in record to use it later  
    SELECT *  
     INTO LREC$SEQUENCE_REC  
     FROM USER_SEQUENCES  
     WHERE SEQUENCE_NAME = IN_SEQUENCE_NAME;  
    LN$GAP := LN$MAX_VALUE - LN$NEXT_VALUE;  
    --alter sequence by gap value  
    EXECUTE IMMEDIATE  
       'ALTER SEQUENCE '  
      || IN_SEQUENCE_NAME  
      || ' INCREMENT BY '  
      || LN$GAP  
      || ' NOCACHE';  
    EXECUTE IMMEDIATE 'SELECT ' || IN_SEQUENCE_NAME || '.NEXTVAL FROM dual'  
      INTO LN$DUMP;  
    EXECUTE IMMEDIATE  
      'ALTER SEQUENCE ' || IN_SEQUENCE_NAME || ' INCREMENT BY 1 NOCACHE';  
    EXECUTE IMMEDIATE  
       'ALTER SEQUENCE '  
      || IN_SEQUENCE_NAME  
      || ' INCREMENT BY '  
      || LREC$SEQUENCE_REC.INCREMENT_BY  
      || ' CACHE '  
      || LREC$SEQUENCE_REC.CACHE_SIZE;  
   END IF;  
 END RESET_SEQUENCE;  

Calling RESET_SEQUENCE Procedure

 BEGIN  
   RESET_SEQUENCE ('SEQ', 'EMP');  
 END;  

Or

 BEGIN  
   RESET_SEQUENCE ('SEQ', 'EMP', 'EMPNO');  
 END;  

Thanks