-
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 /
Last edited by jeffeben; 08-17-2005 at 12:13 PM.
-
Reply
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.
Last edited by ORACLE; 08-19-2005 at 01:34 AM.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|