CREATE OR REPLACE PROCEDURE reset_sequence_from_table (
seq_name IN VARCHAR2, column_name IN VARCHAR2, table_name IN VARCHAR2) AS
 
cval   INTEGER;
 
BEGIN
 
  EXECUTE IMMEDIATE 'SELECT MAX(' || column_name ||') FROM ' || table_name 
  INTO cval;
 
  cval := cval + 1;
 
  RESET_SEQUENCE(seq_name, cval);
 
END reset_sequence_from_table;
 
-- http://www.psoug.org/reference/sequences.html
CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, startvalue IN PLS_INTEGER) AS
 
cval   INTEGER;
inc_by VARCHAR2(25);
 
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';
 
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' 
  INTO cval;
 
  cval := cval - startvalue + 1;
  IF cval < 0 THEN 
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval); 
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF; 
 
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by ||
  cval;
 
  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual'
  INTO cval;
 
  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name ||
  ' INCREMENT BY 1';
 
END reset_sequence;