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