==== PL/SQL Code for 10g Database ====

This package should be installed as the GGATE user on the 10g Database (Source)\\
Before installing you need to create the database link and tables from [[sequenceother|here]]\\
\\
Function MOL_SEQUENCE_CHECK_LINK contains a database link that may need to be renamed.

<code>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

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;

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@ggmolbb
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;

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;

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 (owner, sequence_name, valueint) values (upper(seq_owner), seq_name, seq_val);
  commit;
END LOOP;
CLOSE MOL_SEQ;
END mol_seqoddeven;

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