Results 1 to 3 of 3

Thread: altering a sequence in dynamic sql

  1. #1
    Join Date
    Jul 2005
    Location
    Woodbridge, VA
    Posts
    9

    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.

  2. #2
    Join Date
    Aug 2005
    Posts
    1

    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.

  3. #3
    Join Date
    Mar 2003
    Posts
    468
    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
  •