Hi,
Oracle : 9.2.0.8.0

We have sequence as below:

[code]
CREATE SEQUENCE TEST_SEQ
START WITH 41554558016
INCREMENT BY 2
MAXVALUE 1000000000000000000000000000
MINVALUE 1
NOCYCLE
CACHE 40
NOORDER;
[/code]

We have written a procedure to increment the sequence value based on another sequence value by comparing the two values.

But when I execute the procedure the sequence is not getting incremented as expected.

Can some one help if I am missing out something here?

Below is the procedure. We always pass p_pk_src_max_value < p_pk_dest_max_value

[code]
CREATE OR REPLACE PROCEDURE test_seq_reset_src(p_pk_src_max_value NUMBER,
p_pk_dest_max_value NUMBER) AS

l_delta_value number := 0;
l_max_value number := 0;
BEGIN
dbms_output.put_line(‘Max sequence number in src:’||p_pk_src_max_value);
dbms__output.put_line(‘Max sequence number in dest:’||p_pk_dest_max_value);

IF p_pk_src_max_value > p_pk_dest_max_value THEN
–l_delta_value := p_pk_src_max_value – p_pk_dest_max_value ;
dbms_output.put_line(‘If src max value greater then delta value:’||l_delta_value);
–l_max_value := p_pk_src_max_value;
ELSE
BEGIN
l_delta_value := p_pk_dest_max_value – p_pk_src_max_value ;
dbms_output.put_line(‘If dest max value greater then delta value:’||l_delta_value);
l_max_value := p_pk_dest_max_value;
IF MOD(l_max_value, 2)= 0 THEN
dbms_output.put_line(‘src MAX value is EVEN and starting new sequence value :’ || l_max_value);
ELSE
dbms_output.put_line(‘src MAX value is ODD and starting sequence value :’ || l_max_value);
l_delta_value := l_delta_value + 1;
END IF;

EXECUTE IMMEDIATE ‘ALTER SEQUENCE TEST_SEQ INCREMENT BY ‘|| l_delta_value;
dbms_output.put_line(‘TEST_SEQ increment by delta value:’||l_delta_value);
EXECUTE IMMEDIATE ‘select TEST_SEQ.nextval from dual’; /*!!! Replace this line !!!*/
EXECUTE IMMEDIATE ‘ALTER SEQUENCE TEST_SEQ INCREMENT BY 2’;
END;
END IF;
END TEST_SEQ_RESET_src;
[/code]