sql - Generating 6 digit unique random number generator sequence in oracle -

i have tried using noorder clause in oracle sql still getting generated sequence in ascending order.

below sequence creation script

create sequence otp_seq      minvalue 100000 maxvalue 999999      increment 1 nocycle noorder; 

when run below command repeatedly:

select otp_seq.nextval dual; 

it gives values in sequence:

100000 100001 100002 

what want values generated randomly given domain i.e. between minvalue , maxvalue , should unique.

regarding noorder clause, the documentation says:

"specify noorder if not want guarantee sequence numbers generated in order of request. "

the key word guarantee. noorder not promise randomness, means nextval may generate numbers out of order. of concern in rac environments each node has cache of sequence numbers; in these scenarios noorder means cannot infer sequence of nextval requests sequence of given values i.e. cannot use numbers sort records in order of creation.

on requirements.

your requirements contradictory. randomness means unpredictability. uniqueness means predictability.

you cannot implement sequence build own thing this:

create table pseudo_sequence (     used varchar2(1) default 'n' not null     , id number not null     , next_val number not null     , primary key (used, id)     ) organization index / 

note index table syntax. next trick populate table randomly.

insert pseudo_sequence (id, next_val) nbr (     select level + 99999 nx     dual     connect level <= 900000     order dbms_random.value   ) select rownum, nx nbr /    

we need id column preserve random distribution of next_val across table; without index impose order, , want avoid sorting every time query.

next build query next value table, , mark used:

create or replace function random_nextval     return number     pragma autonomous_transaction;     cursor ps         select next_val          pseudo_sequence         used = 'n'         , rownum = 1         update of used skip locked;     return_value number; begin    open ps;    fetch ps return_value;    update pseudo_sequence    set used = 'y'    current of ps;    close ps;    commit;   return return_value; end; / 

and here how works:

sql> select random_nextval dual   2  connect level <= 5   3  /     random_nextval --------------         216000         625803         806843         997165         989896  sql> select * pseudo_sequence used='y'   2  /  u         id   next_val - ---------- ---------- y          1     216000 y          2     625803 y          3     806843 y          4     997165 y          5     989896  sql> select random_nextval dual   2  connect level <= 5   3  /  random_nextval --------------         346547         911900         392290         712611         760088  sql> 

of course, argue not random next value predictable looking @ underlying table perhaps it's enough needs. won't make promises scalability in multi-user environment, given numberspace scant 900,000 values figure that's not major concern anyway.