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;)
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.
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?
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.
CREATE OR REPLACE TRIGGER "T_USERS_BIR"
BEFORE INSERT ON USERS
FOR EACH ROW
IF :new.USER_ID IS NULL THEN
select USERID_SEQ.nextval into :new.USER_ID from dual;
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.