# Database Discussions > Oracle >  altering a sequence in dynamic sql

## jeffeben

Hi,

I am writing a script to alter a sequence using Dynamic SQL. It includes the following steps:

1.) Find the MAX(abc_id) in table abc_elements;
2.) Find the last_number (in table user_sequences) for sequence abc_seq;
3.) If MAX(abc_id) < last_number do nothing;
4.) If MAX(abc_id) >= last_number do the following:

a.) Calculate MAX(abc_id) - last_number (that's a minus sign);
b.) Alter the sequence to increment by the difference;
c.) Select abc_seq.NEXTVAL from dual;
d.) Alter the sequence to increment by 1.

I have successfully written and compiled the script, as shown below, but I think I need another piece of code that corresponds to #3 above. As the code runs presently, even when MAX(abc_id) < last_number, when nothing should happen, the sequence will still increment because I am using NEXTVAL; I don't want this to happen. I have tried SELECTing last_number INTO v_last_number but this does not work correctly, either, as the next number in the sequence is now MAX(abc_id). This happens even when MAX(abc_id) >= last_number (#4 above).

I think I may be missing one piece of code. I would appreciate any assistance in this.

Thanks,
Jeff

(code begins here)

scott@ORA92> DECLARE
  2    v_max_abc_id  abc_elements.abc_id%TYPE;
  3    v_last_number NUMBER;
  4    v_difference  NUMBER;
  5  BEGIN
  6    SELECT MAX(abc_id)
  7    INTO   v_max_abc_id
  8    FROM   abc_elements;
  9  
 10    SELECT abc_seq.NEXTVAL
 11    INTO   v_last_number
 12    FROM   user_sequences
 13    WHERE  sequence_name = 'ABC_SEQ';
 14  
 15    IF v_max_abc_id >= v_last_number THEN
 16  	 v_difference := (v_max_abc_id - v_last_number);
 17  	 EXECUTE IMMEDIATE 'ALTER SEQUENCE ABC_SEQ INCREMENT BY '|| v_difference;
 18  	 SELECT ABC_SEQ.NEXTVAL INTO v_last_number FROM dual;
 19  	 EXECUTE IMMEDIATE 'ALTER SEQUENCE ABC_SEQ INCREMENT BY 1';
 20    END IF;
 21  END;
 22  /

----------


## ORACLE

Instead of using abc_seq.nextval,
USE
SELECT LAST_NUMBER FROM USER_SEQUENCES
WHERE SEQUENCE_NAME ='ABC_SEQ';
This will return the next value for sequence.So NEXTVAL can be avoided.
If you still have some problem post back.

----------


## jkoopmann

you need to be VERY careful with using LAST_NUMBER from user_sequences. This is not always the last number used through the use of NEXTVAL. 

This is dependent on the CACHE_SIZE of the sequence in question.

for instance:



```
SQL> create sequence seq_1 increment by 1;

Sequence created.

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         1

SQL> select last_number, cache_size from user_sequences where sequence_name = 'SEQ_1';

LAST_NUMBER CACHE_SIZE
----------- ----------
         21         20

SQL> select seq_1.nextval from dual;

   NEXTVAL
----------
         2
```

So this could be the reason why you are having problems in your code as you are using the LAST_NUMBER in the calculations. Just go and check if your CACHE_SIZE is greater than 1.

if you want to reset a sequence number based on values in a table, there is no way around using NEXTVAL to find out what the true setting of a sequence is. Don't worry about this as your code should reset everything. 

for instance :
if high ID in table is 10
select nextval from sequence will result in 11
set increment by -1
select nextval from dual (this will select 10 off sequence)
set increment by 1 (next sequence will be 11)

hope this helps.

----------

