altering a sequence in dynamic sql

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 /

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.

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:

Code:

---

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.