Skip to main content
GoldenGate

How to update Sequences in Source and Target for Golden Gate Bi-Directional setup

By December 12, 2021August 28th, 2022No Comments3 min read

Having odd sequences in one site and an even sequence in another site is a basic prerequisite for the Golden Gate. Please note that there is no rule that even should be in source and odd should be in target. It could be vice versa.
To implement odd/even sequences in each site, the below code can be used. This code will take care of all the sequences of the schema.

Sample Code to increment using ODD

declare
seqown varchar2(30);
seqname varchar2(30);
sqlstmt varchar2(1000);
cval number;
incr number;
cursor seq_cur is
select sequence_owner, sequence_name
from dba_sequences
where sequence_owner = 'SCOTT';
begin
open seq_cur;
loop
fetch seq_cur into seqown, seqname;
exit when seq_cur%notfound;
sqlstmt := 'select '||seqown||'.'||seqname||'.nextval from dual';
execute immediate sqlstmt into cval;

if ( mod(cval, 2) = 0 )
then
-- ensure the current val is odd number
-- first change increment by 1
sqlstmt := 'alter sequence '||seqown ||'.'||seqname||' increment by 1';
execute immediate sqlstmt;

sqlstmt := 'select '||seqown||'.'||seqname||'.nextval from dual';
execute immediate sqlstmt into cval;
sqlstmt := 'alter sequence '||seqown||'.'||seqname||' increment by 2';
execute immediate sqlstmt;
else
-- already an odd number
sqlstmt := 'alter sequence '||seqown||'.'||seqname||' increment by 2';
execute immediate sqlstmt;
end if;
end loop;
close seq_cur;
end;
/

Sample Code to increment using EVEN

declare
seqown varchar2(30);
seqname varchar2(30);
sqlstmt varchar2(1000);
cval number;
incr number;
cursor seq_cur is
select sequence_owner, sequence_name
from dba_sequences
where sequence_owner ='SCOTT';
begin
open seq_cur;
loop
fetch seq_cur into seqown, seqname;
exit when seq_cur%notfound;
sqlstmt := 'select '||seqown||'.'||seqname||'.nextval from dual';
execute immediate sqlstmt into cval;

if ( mod(cval, 2) > 0 )
then
-- ensure the current val is odd number
-- first change increment by 1
sqlstmt := 'alter sequence '||seqown ||'.'||seqname||' increment by 1';
execute immediate sqlstmt;

sqlstmt := 'select '||seqown||'.'||seqname||'.nextval from dual';
execute immediate sqlstmt into cval;
sqlstmt := 'alter sequence '||seqown||'.'||seqname||' increment by 2';
execute immediate sqlstmt;
else
-- already an odd number
sqlstmt := 'alter sequence '||seqown||'.'||seqname||' increment by 2';
execute immediate sqlstmt;
end if;
end loop;
close seq_cur;
end;
/

Leave a Reply