/**
********************************************************************************

SCRIPT NAME:  fix_seq.sql 
PURPOSE:      Set a specified Oracle sequence so that it's value is at least 
              equal to the maximum value or a specified table column
AUTHOR:       Hugh Reinhart
DATE:         August 2007 

********************************************************************************
**/

PROMPT Column = &2..&1.     Sequence = &3.

SET SERVEROUTPUT ON
DECLARE
  MAXID NUMBER;
  CURR  NUMBER;
BEGIN
  SELECT NVL(MAX(&1.),0)
  INTO MAXID
  FROM &2.;
  
  SELECT &3..NEXTVAL 
    INTO CURR 
    FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('CURR = '||CURR||'    MAXID = '||MAXID);    

  IF(CURR < MAXID)THEN
    EXECUTE IMMEDIATE 'ALTER SEQUENCE &3. INCREMENT BY ' || (MAXID - CURR);
    SELECT &3..NEXTVAL 
      INTO CURR 
      FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('CURR = '||CURR||'    MAXID = '||MAXID); 
    EXECUTE IMMEDIATE 'ALTER SEQUENCE &3. INCREMENT BY 1';  
  END IF;  
END;
/

SET SERVEROUTPUT OFF



