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
Like this:
Like Loading...
