APEXtras

a development team dedicated to Oracle APEX

Reset or Create Sequences and Triggers

leave a comment »

create or replace package APEXTRAS_RESET_TRIG_SEQ is

  -- Author  : Roger Cohen
  -- Created : 01/06/2009 13:07:08
  -- Purpose : Demonstrate reset or recreate all sequences 
  --           and triggers needed for surrogate primary keys

  FUNCTION ResetSequences(p_tablespace  IN VARCHAR2,
                          p_tableprefix IN VARCHAR2,
                          p_tablelist   IN VARCHAR2) RETURN CLOB;

end APEXTRAS_RESET_TRIG_SEQ;
/
create or replace package body APEXTRAS_RESET_TRIG_SEQ is

  function get_delimited_token(p_the_list  IN CLOB,
                               p_the_index IN NUMBER,
                               p_delim1    IN VARCHAR2 := '[',
                               p_delim2    IN VARCHAR2 := ']') return VARCHAR2 IS
    l_start_pos number;
    l_end_pos   number;
    l_index     number;
    l_output    VARCHAR2(32767);
  begin
    if (p_delim1 = p_delim2) then
      l_index := p_the_index - 1;
    else
      l_index := p_the_index;
    end if;    
    if (p_the_index = 1) then
      if (p_delim1 = p_delim2) then
        l_start_pos := 1;
      else
        l_start_pos := instr(p_the_list,p_delim1,1,1)  + length(p_delim1);
      end if;
    else
      l_start_pos := instr(p_the_list,p_delim1,1,l_index);
      if l_start_pos = 0 then
          l_output := null;
      else
          l_start_pos := l_start_pos + length(p_delim1);
      end if;
    end if;
    if (l_start_pos != 0) then
      l_end_pos := instr(p_the_list,p_delim2,l_start_pos,1);

      if l_end_pos = 0 then
          l_output := substr(p_the_list,l_start_pos);
      else
          l_output := substr(p_the_list,l_start_pos,l_end_pos - l_start_pos);
      end if;
    end if;
    return l_output;

  end get_delimited_token;
  
  function get_token(p_the_list  IN CLOB,
                     p_the_index IN NUMBER,
                     p_delim     IN VARCHAR2 := ',') return VARCHAR2 IS
    l_output VARCHAR2(32767);
  begin
    l_output := get_delimited_token(p_the_list,p_the_index,p_delim,p_delim);

    return l_output;
  end get_token;

  FUNCTION LongestMatch(p_s1 IN VARCHAR2,
                        p_s2 IN VARCHAR2) RETURN VARCHAR2 IS
    l_pos    INTEGER;
    l_length INTEGER;
  BEGIN
    l_length := LEAST(LENGTH(p_s1),LENGTH(p_s2));
    l_pos := 1;
    WHILE ((SUBSTR(p_s1,l_pos,1) = SUBSTR(p_s2,l_pos,1)) AND (l_pos <= l_length))
    LOOP
      l_pos := l_pos + 1; 
    END LOOP;
    RETURN SUBSTR(p_s1,1,l_POS - 1);
  END;

  PROCEDURE SetSeq(p_name       IN VARCHAR2,
                   p_tablespace IN VARCHAR2,
                   p_val        IN NUMBER) IS
    l_num            NUMBER;
    l_cache_size     INTEGER;
    l_cache_default  INTEGER := 20;
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    SELECT CACHE_SIZE INTO l_cache_size
    FROM all_sequences alls
    WHERE ((alls.sequence_name = p_name)
    AND (alls.sequence_owner = p_tablespace));

    IF (l_cache_size = 0) THEN
      l_cache_size := l_cache_default;
    END IF;
    EXECUTE IMMEDIATE 'alter sequence ' || p_tablespace || '.' || p_name || 
                      ' INCREMENT BY 1 NOCACHE';
    EXECUTE IMMEDIATE 'SELECT ' || p_tablespace || '.' || p_name || '.NEXTVAL FROM DUAL' INTO l_num;
    IF (l_num < p_val) THEN
      IF (l_num < (p_val - 1)) THEN
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_tablespace || '.' || p_name || 
                          ' INCREMENT BY ' || TO_CHAR((p_val - l_num - 1)) || ' MINVALUE 1';
        EXECUTE IMMEDIATE 'SELECT ' || p_tablespace || '.' || p_name || '.NEXTVAL FROM DUAL' INTO l_num;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_tablespace || '.' || p_name || ' INCREMENT BY 1';
      ELSIF (l_num = (p_val - 1)) THEN
        EXECUTE IMMEDIATE 'SELECT ' || p_tablespace || '.' || p_name || '.NEXTVAL FROM DUAL' INTO l_num;
      END IF;
    END IF;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || p_tablespace || '.' || p_name || 
                      ' INCREMENT BY 1 CACHE ' || TO_CHAR(l_cache_size);
    COMMIT;
  END;


  PROCEDURE CreateSeqTrigger(p_table      IN VARCHAR2,
                             p_tablespace IN VARCHAR2,
                             p_column     IN VARCHAR2,
                             p_seq        IN VARCHAR2,
                             p_trigger    IN VARCHAR2) IS
    l_sql VARCHAR2(32767);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    l_sql := ' CREATE OR REPLACE TRIGGER ' || p_tablespace || '.' || p_trigger                                                   ||
             ' BEFORE INSERT ON '          || p_tablespace || '.' || p_table                                                     ||
             ' FOR EACH ROW '                                                                                                    ||
             ' WHEN (NEW.'                 || p_column  || ' IS NULL) BEGIN'                                                     ||
             '   SELECT '                  || p_tablespace || '.' || p_seq || '.NEXTVAL INTO :NEW.'||  p_column || ' FROM DUAL;' ||
             ' END;';
    BEGIN
      EXECUTE IMMEDIATE l_sql;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    COMMIT;
  END;     

  PROCEDURE CreateSequence(p_seq        IN VARCHAR2,
                           p_tablespace IN VARCHAR2,
                           p_start      IN NUMBER) IS
    l_sql    VARCHAR2(32767);
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    l_sql := ' CREATE sequence ' || p_tablespace || '.' || p_seq ||     
             ' START WITH ' || TO_CHAR(p_start) ||
             ' INCREMENT BY 1 ' ||
             ' CACHE 20';
    BEGIN
      EXECUTE IMMEDIATE l_sql;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
    COMMIT;
  END;     

  FUNCTION ResetSequences(p_tablespace  IN VARCHAR2,
                          p_tableprefix IN VARCHAR2,
                          p_tablelist   IN VARCHAR2) RETURN CLOB IS
    l_sequence_name     VARCHAR2(1000);
    l_new_sequence_min  NUMBER;
    l_sql               VARCHAR2(1000);
    l_tablestem         VARCHAR2(30);
    l_sequences_reset   CLOB;
    l_sequences_created CLOB;
    l_triggers_created  CLOB;
    l_delimiter         VARCHAR2(2)    := UTL_TCP.CRLF;
    l_match_length      INTEGER;
    l_tableindex        INTEGER        := 1;
    l_tablename         VARCHAR2(100);
    l_tableprefix       VARCHAR2(30);
  BEGIN
    l_tablename := UPPER(get_token(p_tablelist,l_tableindex));
    IF (l_tablename IS NULL) THEN
      l_tableprefix := UPPER(p_tableprefix);
    END IF;
    -- The outer loop steps through the comma separated list of tables in p_tablelist
    -- If p_tablelist is null, the outer loop is executed once only (l_tableprefix
    -- is set to NULL at the end of the loop)
    WHILE ((l_tablename IS NOT NULL) OR (l_tableprefix IS NOT NULL))
    LOOP
      -- The inner loop steps through the primary keys that belong
      -- to every matching table
      -- Matching tables are:
      -- IF l_tablename is not NULL then l_tablename
      -- ELSE all tables that start l_tableprefix || '%'
      FOR I IN (SELECT trig_seq.SEQUENCE_NAME  AS SEQUENCE_NAME,
                       trig_table.TRIGGER_NAME AS TRIGGER_NAME,
                       allc.TABLE_NAME         AS TABLE_NAME,
                       allic.COLUMN_NAME       AS COLUMN_NAME
                FROM all_constraints allc -- All primary keys (constraint_type = 'P')
                INNER JOIN all_ind_columns allic ON (allc.index_name = allic.index_name)
                -- This subquery returns all triggers that reference tables
                -- The join gives the trigger (if any) of type 'BEFORE EACH ROW'
                -- and of column usage NEW and OUT
                -- and that fires on INSERT that references one table
                LEFT OUTER JOIN (SELECT d1.referenced_name  AS TABLE_NAME,
                                        d1.name             AS TRIGGER_NAME,
                                        d1.referenced_owner AS REFERENCED_OWNER,
                                        atc.column_name     AS COLUMN_NAME
                                 FROM all_dependencies d1
                                 INNER JOIN all_triggers at ON ((d1.name = at.trigger_name) 
                                                            AND (at.table_owner = p_tablespace)
                                                            AND (at.trigger_type = 'BEFORE EACH ROW') 
                                                            AND (at.triggering_event = 'INSERT'))
                                 INNER JOIN all_trigger_cols atc ON ((d1.name = atc.trigger_name) 
                                                                 AND (atc.table_owner = p_tablespace)
                                                                 AND (atc.column_usage LIKE '%NEW%')
                                                                 AND (atc.column_usage LIKE '%OUT%'))
                                 WHERE ((d1.type = 'TRIGGER')
                                 AND (d1.referenced_owner = p_tablespace)
                                 AND (d1.referenced_type = 'TABLE'))) trig_table 
                                      ON ((allc.TABLE_NAME = trig_table.TABLE_NAME) AND (allic.COLUMN_NAME = trig_table.COLUMN_NAME))
                -- This subquery returns all triggers that reference sequences
                -- The join gives us the sequence (if any) referenced by one trigger
                LEFT OUTER JOIN (SELECT d2.referenced_name AS SEQUENCE_NAME,
                                        d2.name            AS TRIGGER_NAME
                                 FROM all_dependencies d2
                                 WHERE ((d2.type = 'TRIGGER')
                                 AND (d2.referenced_owner = p_tablespace)
                                 AND (d2.referenced_type = 'SEQUENCE'))) trig_seq 
                                     ON (trig_table.TRIGGER_NAME = trig_seq.TRIGGER_NAME)
                -- This subquery returns all triggers that reference views
                -- The join gives us the view (if any) referenced by one trigger
                LEFT OUTER JOIN (SELECT d3.referenced_name AS VIEW_NAME,
                                        d3.name            AS TRIGGER_NAME
                                 FROM all_dependencies d3
                                 WHERE ((d3.type = 'TRIGGER')
                                 AND (d3.referenced_owner = p_tablespace)
                                 AND (d3.referenced_type = 'VIEW'))) trig_view 
                                     ON (trig_table.TRIGGER_NAME = trig_view.TRIGGER_NAME)
               WHERE ((allc.owner = p_tablespace) -- Filter by tablespace
               AND (trig_view.VIEW_NAME IS NULL)  -- Exclude triggers that relate to updateable views
               AND (((UPPER(allc.table_name) = l_tablename) AND (l_tablename IS NOT NULL)) -- Just one table if one is specified
                    OR ((UPPER(allc.table_name) LIKE l_tableprefix || '%') AND (l_tableprefix IS NOT NULL)))
               AND (allc.constraint_type = 'P')) -- Only constraints that are primary keys
               ORDER BY trig_table.TABLE_NAME)
      LOOP
        l_sql := 'SELECT MAX(' || I.COLUMN_NAME || ') FROM ' || p_tablespace || '.' || I.TABLE_NAME;
        EXECUTE IMMEDIATE l_sql INTO l_new_sequence_min;
        l_new_sequence_min := NVL(l_new_sequence_min,0) + 1;
        IF (I.SEQUENCE_NAME IS NOT NULL) THEN
          -- The trigger and sequence both exist, so just reset the sequence
          SetSeq(I.SEQUENCE_NAME,p_tablespace,l_new_sequence_min);
          l_sequences_reset := l_sequences_reset || I.SEQUENCE_NAME || 
                               ' Next Value Set To ' || TO_CHAR(l_new_sequence_min) || l_delimiter;
        ELSIF ((I.TRIGGER_NAME IS NULL) OR (I.SEQUENCE_NAME IS NULL)) THEN
          -- One or both of the trigger and sequence must be recreated

          -- l_tablestem will form the start of the sequence and trigger name
          -- If the table name starts with the same characters as the table prefix, 
          -- then remove those characters from l_tablestem
          IF (INSTR(UPPER(I.TABLE_NAME),UPPER(p_tableprefix)) = 1) THEN
            l_tablestem := UPPER(SUBSTR(I.TABLE_NAME,LENGTH(p_tableprefix) + 1));
          ELSE
            l_tablestem := UPPER(I.TABLE_NAME);
          END IF;
          -- If l_tablestem and the column name now have characters 1 - 4 in 
          -- common then set l_tablestem to NULL and just use the column name
          -- as the name of the sequence and trigger
          l_match_length := LENGTH(LongestMatch(I.COLUMN_NAME,l_tablestem));
          IF (l_match_length >= 4) THEN
            l_tablestem := '';
          ELSE
            l_tablestem := l_tablestem || '_';
          END IF;
          l_sequence_name := SUBSTR(l_tablestem || I.COLUMN_NAME,1,30);
          CreateSequence(l_sequence_name,p_tablespace,l_new_sequence_min);
          l_sequences_created := l_sequences_created || l_sequence_name || l_delimiter;
          IF (I.TRIGGER_NAME IS NULL) THEN
            CreateSeqTrigger(I.TABLE_NAME,p_tablespace,I.COLUMN_NAME,l_sequence_name, 
                             SUBSTR(l_tablestem || I.COLUMN_NAME,1,30));
            l_triggers_created := l_triggers_created || SUBSTR(l_tablestem || I.COLUMN_NAME,1,30) || l_delimiter;
          END IF;
        END IF;
      END LOOP;
      l_tableindex   := l_tableindex + 1;
      l_tablename    := UPPER(get_token(p_tablelist,l_tableindex));
      l_tableprefix  := NULL;
    END LOOP;
    RETURN 'Sequences Reset:   ' || l_delimiter || l_sequences_reset   || l_delimiter ||
           'Sequences Created: ' || l_delimiter || l_sequences_created || l_delimiter ||
           'Triggers Created:  ' || l_delimiter || l_triggers_created  || l_delimiter;
  END;


end APEXTRAS_RESET_TRIG_SEQ;
/

Automatic Oracle Sequence Regeneration

Written by Roger

4 June, 2009 at 4:55 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: