DB Wiki

This is an old revision of the document!

---

-- 10g Package
create or replace package MOL_SEQUENCES
AUTHID CURRENT_USER
as
   FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2) RETURN number;
   FUNCTION mol_sequence_check_link (seq_owner in varchar2, seq_name in varchar2) RETURN number;
   PROCEDURE mol_sequence_sync (seq_owner in varchar2, seq_name in varchar2, sync_value in number);
   PROCEDURE mol_seqoddeven (seq_owner varchar2);
   PROCEDURE mol_inc1 (seq_owner varchar2);
end MOL_SEQUENCES;
/

create or replace package body MOL_SEQUENCES as

-- Check Sequence Value on 10g Database
FUNCTION mol_sequence_check (seq_owner in varchar2, seq_name in varchar2)
RETURN NUMBER
AS
seq_number number(12);
BEGIN
select last_number into seq_number
from all_sequences
where sequence_owner = upper(seq_owner) 
and sequence_name = upper(seq_name);
return seq_number;
EXCEPTION
  when no_data_found then
    return -1;
  when others then raise;
end mol_sequence_check;

-- Check Sequence Value on 12c Database
FUNCTION mol_sequence_check_link (seq_owner IN varchar2, seq_name IN varchar2)
RETURN NUMBER
AS
seq_number number(12);
begin
select last_number into seq_number
from all_sequences@ggpoc
where sequence_owner = upper(seq_owner) 
and sequence_name = upper(seq_name);
return seq_number;
EXCEPTION
  when no_data_found then
    return -1;
  when others then raise;
end mol_sequence_check_link;

-- Find difference in values between 10g and 12c. If greater than sync_value then move sequence value closer
PROCEDURE mol_sequence_sync (seq_owner IN varchar2, seq_name IN varchar2, sync_value IN number)
AS
v_seq_diff number(12);
v_local_val number(12);
BEGIN
-- Get difference between both sequence values
select seq_check_link(seq_owner, seq_name) - seq_check(seq_owner, seq_name)
into v_seq_diff
from dual;
-- If difference is more than stated amount then resynchronise values between 2 databases
If v_seq_diff > sync_value
  then for x in 1..round(v_seq_diff/2,0)
    LOOP
      execute immediate 'select ' || seq_owner || '.' || seq_name || '.nextval from dual' into v_local_val;
    END LOOP;
      insert into ggate.sync_sequences_log values (seq_owner, seq_name, v_seq_diff, SYSTIMESTAMP);
      commit;
End If;
END mol_sequence_sync;

-- Create Sequence Value table for named schema
PROCEDURE mol_seqoddeven (seq_owner IN varchar2)
AS
seq_name varchar2(100);
seq_val number(10);
cursor MOL_SEQ is
select sequence_name from all_sequences
where sequence_owner = upper(seq_owner);
BEGIN
OPEN MOL_SEQ;
LOOP
  FETCH MOL_SEQ into seq_name;
  EXIT WHEN MOL_SEQ%NOTFOUND;
  execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by 2';
  execute immediate 'select ' || seq_owner || '.' || seq_name || '.nextval  from dual' into seq_val;
  insert into mol_gg_sequences (seq_owner, seq_name, valueint) values (upper(seq_owner), seq_name, seq_val);
  commit;
END LOOP;
CLOSE MOL_SEQ;
END mol_seqoddeven;

-- Reset all sequences back to increment 1
PROCEDURE mol_inc1 (seq_owner IN varchar2)
AS
vSeqName varchar2(100);
cursor SeqName is
select sequence_name from all_sequences
where sequence_owner = upper(seq_owner);
BEGIN
open SeqName;
loop
  fetch SeqName into vSeqName;
  exit when SeqName%NOTFOUND;
    execute immediate 'alter sequence ' || seq_owner || '.' || vSeqName || ' increment by 1';
end loop;
close SeqName;
END mol_inc1;

END MOL_SEQUENCES;
/