7 Replies Latest reply on Jun 16, 2008 7:49 PM by Billy~Verreynne

    Sequence

    coco
      Hi,
      I want to create a sequence on a table A and every time when I insert a row to be automatic generated a number. this table has already data. I wrote the following:"CREATE SEQUENCE USERID_SEQ
      INCREMENT BY 1
      START WITH (select max(to_number(USER_ID))+1 FROM USERS;)
      NOCACHE
      ORDER" and I have an error: invalid number;
      I also created a sequence which start from the max(user_id) but I do not know how can I assign this to column user_id.
      can someone help me with this.

      Thank you
        • 1. Re: Sequence
          637222
          I think you cannot use
          select max(to_number(USER_ID))+1 FROM USERS
          in "Start With" clause. It will take a numeric figure.
          • 2. Re: Sequence
            coco
            do you have any other idea?
            • 3. Re: Sequence
              636403
              declare
              l_uid number;
              l_count number;
              begin
              select nvl(max(USER_ID),0), count(1)
              into l_uid, l_count
              from USERS;

              execute immediate replace('CREATE SEQUENCE USERID_SEQ
              INCREMENT BY 1
              START WITH %1
              MINVALUE 0
              NOCACHE
              ORDER', '%1', l_uid);
              end;
              • 4. Re: Sequence solved
                coco
                tank you.
                • 5. Re: Sequence
                  Billy~Verreynne
                  And when you SELECT MAX ran, a process has already inserted another million rows, each with a unique ID.

                  And just before you run the EXECUTE IMMEDIATE, that process committed its transaction.

                  Result - the sequence is created incorrectly and is out by a figure of a million or so.

                  Oracle is a multi user and multi processing platform.

                  When designing and when writing code, always but always think about "thread safety". What will happen if some other process change the data I'm looking at? What will happen when another copy of the same code is executed by another process?
                  • 6. Re: Sequence
                    636403
                    Cause you are right! I supposed that the author guess about the effects you've pointed.

                    The pointed anonymouse block must be used only once when the sequence is created and there must not be any modification on the table till the trigger is created.

                    In example:

                    CREATE OR REPLACE TRIGGER "T_USERS_BIR"
                    BEFORE INSERT ON USERS
                    FOR EACH ROW

                    BEGIN
                    IF :new.USER_ID IS NULL THEN
                    select USERID_SEQ.nextval into :new.USER_ID from dual;
                    END IF;
                    END T_USERS_BIR;
                    • 7. Re: Sequence
                      Billy~Verreynne
                      The basic issue is that it is not really sensible to dynamically create a sequence.. especially not by selecting the MAX(id) of a table and then start the sequence at MAX(id)+1 - as the table can change while doing that.

                      One needs to not only lock the table when doing that (creating the sequence), but also prevent any other transactions before the fix (using that sequence - like a trigger) is in place.