This content has been marked as final. Show 7 replies
I think you cannot use
select max(to_number(USER_ID))+1 FROM USERS
in "Start With" clause. It will take a numeric figure.
do you have any other idea?
select nvl(max(USER_ID),0), count(1)
into l_uid, l_count
execute immediate replace('CREATE SEQUENCE USERID_SEQ
INCREMENT BY 1
START WITH %1
ORDER', '%1', l_uid);
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.