Skip to Main Content
  • Questions
  • To generate list of unique Random Numbers based on the number count required

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ahamed.

Asked: March 29, 2018 - 3:18 am UTC

Last updated: November 27, 2020 - 7:18 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

Hi,

My requirement is to generate the list of random numbers based on the total count provided. for instance, if total count is 100, i have to generate 100 unique random numbers. Below is the sample code i used. Could you please check and let me know will the below code is fine.
I have created table temp1 with unique constraint to store the numbers

declare
nTotalNumber number:=100;
nIloop number;
begin
insert into temp(number) values (nTotalNumber);
SAVEPOINT do_escape;
for i in 1..nTotalNumber loop
nIloop := i;
insert into temp1(random_values) values (round(DBMS_RANDOM.value(100000,999999)));
commit;
end loop;
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
   nNumCards :=nTotalNumber-nIloop+1;
   ROLLBACK TO do_escape;
end;


and Connor said...

You could do something like this

SQL> with t as
  2  ( select round(DBMS_RANDOM.value(100000,999999)) r1,
  3           DBMS_RANDOM.value r2
  4    from   dual
  5    connect by level <= 10000
  6  )
  7  select *
  8  from (
  9    select r1,
 10           dense_rank() over ( order by r2 ) rk,
 11           row_number() over ( partition by r1 order by null ) as seq
 12    from t
 13  )
 14  where rk <= 100
 15  and   seq = 1
 16  and   rownum <= 100;

        R1         RK        SEQ
---------- ---------- ----------
    117157          1          1
    155961          2          1
    962572          3          1
    932538          4          1
    120104          5          1
    998174          6          1
    315563          7          1
    895293          8          1
    348776          9          1
    693899         10          1
    427596         11          1
    203730         12          1
    386350         13          1
    786456         14          1
    957763         15          1
    637613         16          1
    764065         17          1
    463862         18          1
    473779         19          1
    871045         20          1
    935234         21          1
    889382         22          1
    149162         23          1
    981742         24          1
    287217         25          1
    610881         26          1
...
...


which is

- get 10,000 random numbers
- get at least the "first" 100 based on a separate random ordering sequence (dense_rank)
- just in case there were any dups, only keep the first in sequence (row_number)

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

without analytics

Igor, April 05, 2018 - 3:13 pm UTC

Apparently it can be done without analytics. Am i missing anything?

SQL> with t as
  2  ( select round(DBMS_RANDOM.value(100000,999999)) v
  3    from   dual
  4    connect by level <= 10000
  5  )
  6  select *
  7  from (
  8    select v
  9      from t
 10      group by v
 11      having count(*) = 1
 12  )
 13  where rownum <= 100;

         V
----------
    636783
    242534
    652196
    445016
    813455
    353460
    119531
etc.

Connor McDonald
April 06, 2018 - 3:49 am UTC

That should be fine as well, or a 'distinct' in the inline view. Lots of ways to do it.

Replace a sequence with a call to generate unique values

Durga, November 25, 2020 - 3:25 pm UTC

Hello Connor,

We have a sequence RQS_SESSION

CREATE SEQUENCE "PROD"."RQS_SESSION" MINVALUE 1004 MAXVALUE 999999999999999999 INCREMENT BY 10 START WITH 819597576284 CACHE 50000 NOORDER CYCLE NOKEEP GLOBAL ;

We are testing the migration of our one of our databases to Oracle Cloud.
The Oracle Cloud requires that the DB should be in Read-Only mode.

During testing, we caused an exception in Data Guard which identified that we are making a DB change in a call to "NextVal" for a DB sequence at the following line -

Select RQS_Session.NextVal into v_Session_ID from dual;


The current on-prem DB is apparently in Read-Write mode and allows this modification.

But this sequence seems to be a problem for the Cloud DB.

Could you suggest a way so that v_Session_ID could be implemented as a 'unique' value rather than a sequential value (using a DB sequence).
Could we replace the Sequence.NextVal call with a call to generate a unique value.

Please suggest.
Thank you in advance.
Chris Saxon
November 25, 2020 - 5:44 pm UTC

Why exactly is your database in read-only mode? It'll be in read-write mode once you migrate, right?

Replace a sequence with a call to generate unique values

Durga, November 25, 2020 - 8:27 pm UTC

Hello Chris,

We have an on-prem architecture which has 1 Primary DB where all the WRITES happen and has 10 databases which get data continuously replicated from the primary and are designed for the READS.
These 10 DBs are in READ-WRITE mode in the on-prem architecture (but they are designed for READS)
(we have a legacy system)

We are testing migration of on-prem system to Oracle Cloud.
The Oracle Cloud requires that the DB which have been designed for READS should be in Read-Only mode.

So, the testing fails at following SQL statement -

Select RQS_Session.NextVal into v_Session_ID from dual;

As the current on-prem DB is apparently in Read-Write mode, it allows this modification.

But this sequence is a problem for the Cloud DB.

So, how can we implement v_Session_ID so that it fits the use case where our DB has to be in Read-Only mode post migration to Oracle Cloud DB?

Please suggest.
Thank you for your time.


Connor McDonald
November 27, 2020 - 7:18 am UTC

Check out session level sequences.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-SEQUENCE.html#GUID-E9C78A8C-615A-4757-B2A8-5E6EFB130571

Note that these "reset" when the session ends and are unique only within the scope of each session.

Where did you get told this statement:

"The Oracle Cloud requires that the DB which have been designed for READS should be in Read-Only mode."

because I don't think thats correct.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library