DB Wiki

This is an old revision of the document!

---

-- 12c 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_seq_alternate (sequence_owner varchar2);
   PROCEDURE mol_inc1 (seq_owner varchar2);
end MOL_SEQUENCES;
/

create or replace package body MOL_SEQUENCES as

-- Check Sequence Value on 12c 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 10g 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@ggint
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 mol_sequence_check_link(seq_owner, seq_name) - mol_sequence_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;

-- Check if sequence value on 10g is odd or even. Increment sequence by 2 and set up so it is reverse odd/even.
PROCEDURE mol_seq_alternate (sequence_owner IN varchar2)
AS
vSeqValInt   number(12);
vSeqValuePoc number(12);
vSeqName  varchar2(100);
cursor SeqValueInt is
select sequence_name, ValueINT from ggate.mol_gg_sequences@ggint
where upper(owner) = upper(sequence_owner);
BEGIN
open SeqValueInt;
loop
  fetch SeqValueInt into vSeqName, vSeqValInt;
  exit when SeqValueInt%NOTFOUND;
  dbms_output.put_line(vSeqName);
  execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 2';
  execute immediate 'select ' || sequence_owner || '.' || vSeqName || '.nextval  from dual' into vSeqValuePoc;
    if mod(vSeqValInt,2) = mod(vSeqValuePoc,2)
     then
       execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 1';
       execute immediate 'select ' || sequence_owner || '.' || vSeqName || '.nextval  from dual' into vSeqValuePoc;
       execute immediate 'alter sequence ' || sequence_owner || '.' || vSeqName || ' increment by 2';
       update mol_gg_sequences@ggint set ValuePOC = vSeqValuePoc where sequence_name = vSeqName and owner = sequence_owner;
       commit;
     else
       update mol_gg_sequences@ggint set ValuePOC = vSeqValuePoc where sequence_name = vSeqName and owner = sequence_owner;
       commit;
    end if;
end loop;
close SeqValueInt;
END mol_seq_alternate;

-- 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;
/