Procedure to Sync values in Tables with values in Sequences.

=====================================
General
=====================================
Procedure to Sync values in Tables with values in Sequences.
If table value higher then sequence value, the value in sequence is advanced.

=====================================
Code
=====================================

PROCEDURE fix_sequences (p_table_name    IN VARCHAR2, 
                         p_sequence_name IN VARCHAR2) IS

  v_table_max       NUMBER;
  v_sequence_val    NUMBER;
  v_dummy           NUMBER;
  v_exists          NUMBER;
  v_loop            NUMBER;
  v_count_fields_pk NUMBER;
  v_field_name_pk   USER_CONS_COLUMNS.column_name%TYPE;
  v_sql_str         VARCHAR2(4000);

BEGIN

  --Check If Requested Table exists
  SELECT COUNT(*) INTO v_exists
  FROM USER_TABLES
  WHERE table_name = UPPER(p_table_name);
  IF v_exists = 0 Then
     RAISE_APPLICATION_ERROR(-20000,'Requested Table <' || p_table_name || '> does not exists ' || SQLERRM);
  END IF;

   --Check If Requested Sequence exists
  SELECT COUNT(*) INTO v_exists
  FROM USER_SEQUENCES
  WHERE sequence_name = UPPER(p_sequence_name);
  IF v_exists = 0 Then
    RAISE_APPLICATION_ERROR(-20000,'Requested Sequence <' || p_sequence_name || '> does not exists ' || SQLERRM);
  END IF;


  -- Find Field Name of PK for Sequence ...
  SELECT COUNT(*) INTO v_count_fields_pk
  FROM USER_CONSTRAINTS c, 
       USER_CONS_COLUMNS cc
  WHERE c.constraint_type = 'P'
    AND c.constraint_name = cc.constraint_name
    AND c.table_name = UPPER(p_table_name);

   IF v_count_fields_pk = 0 THEN
     RAISE_APPLICATION_ERROR(-20000,'No PK for Requested table <' || p_table_name || '> ' || SQLERRM);
   ELSIF v_count_fields_pk > 1 THEN
     RAISE_APPLICATION_ERROR(-20000,'PK of Requested table <' || p_table_name || '> has more then 1 field ' || SQLERRM);
   ELSE
     NULL; --No problem, Found the correct PK
   END IF;

   BEGIN
     SELECT cc.column_name INTO v_field_name_pk
       FROM USER_CONSTRAINTS C, 
            USER_CONS_COLUMNS CC
   WHERE c.constraint_type = 'P'
     AND c.constraint_name = cc.constraint_name
     AND c.table_name = UPPER(p_table_name);
   EXCEPTION
     WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20000,'Error while finding PK field name for table <' || p_table_name || '> ' || SQLERRM);
   END;

   --Find MaxValue in Table
   v_sql_str := 'SELECT NVL(MAX('||v_field_name_pk||'),0) FROM '||p_table_name;
   EXECUTE IMMEDIATE v_sql_str INTO v_table_max;

   --Find NextVal of Sequence
   v_sql_str := 'SELECT '||p_sequence_name||'.NEXTVAL FROM DUAL';
   EXECUTE IMMEDIATE v_sql_str INTO v_sequence_val;

   --Compare 2 Values AND Move Sequence if Needed
   IF v_table_max > v_sequence_val THEN 
     v_loop := v_table_max - v_sequence_val;
     FOR i IN 0..v_loop LOOP
       v_sql_str := 'SELECT ' || p_sequence_name || '.NEXTVAL FROM DUAL';
       EXECUTE IMMEDIATE v_sql_str INTO v_dummy;
     END LOOP;
   END IF;

END fix_sequences;