1 2 3 Previous Next 32 Replies Latest reply: Mar 21, 2014 6:45 AM by padders RSS

    Unique Random number

    user12050217

      Hi,

      Oracle 11.2.0.1

      Windows

       

      I have a table something like this :

      create table rnd (id number,rnd number);

       

      I need a procedure something like this :

      exec genrnd(200,300);

      So, after executing the procedure there will be 100 rows in the rnd table and there will be unique random numbers in rnd column.  Ok, suppose if I executed again something like this :

      exec genrnd(173,514);

      so table will be truncated, and (514-173)+1=342 rows will be in rnd table and new unique random numbers will be in rnd column.

       

      Id column is just for purpose of order by.  It will have 1,2,3,....100 or 1,2,3,4,....342 numbers.

       

      I can get random number by this way :

      select trunc(dbms_random.value(200,300)) rnd from dual;

      select trunc(dbms_random.value(173,514)) rnd from dual;

      but thing number should be unique in the given range, so I am not getting how this can be achieved, please help me.

       

      Thanks.

        • 1. Re: Unique Random number
          Nimish Garg

          Try this:

           

          create or replace procedure genrnd(start_value number, end_value number)

          as

          begin

            insert into rnd

            select rownum, rownum + start_value -1

            from dual connect by rownum <= end_value - start_value + 1;

            commit;

          end;

          /

          • 2. Re: Unique Random number
            user12050217

            Thanks for your reply, but this is not what I am looking, because it just generated a series of numbers from 173 to 514; while I need numbers should be in random order please.  Numbers should be randomized but only once a time in the table.

            • 4. Re: Unique Random number
              Nimish Garg

              try this

               

              create or replace procedure genrnd(start_value number, end_value number)

              as

              begin

                delete from rnd;

                commit;

               

                insert into rnd

                select rownum, a from

                (

                  select * from

                  (

                    select rownum + start_value - 1 a

                    from dual connect by rownum <= end_value - start_value + 1

                  )

                  order by dbms_random.value

                );

                commit;

              end;

              /

              • 5. Re: Unique Random number
                Moazzam

                May be you are looking for this:

                CREATE OR REPLACE PROCEDURE genrnd(start_value number, end_value number)

                AS

                BEGIN

                  INSERT INTO rnd

                  SELECT ROWNUM,round(dbms_random.value*80,2)

                  FROM dual connect BY ROWNUM <= end_value - start_value + 1;

                  COMMIT;

                END;

                /

                • 6. Re: Unique Random number
                  user12050217

                  Thanks again.  It worked but will it always generate unique random numbers, because in your code, I don't see any checking of duplicates; i.e. something like :

                   

                  If this number is already in the table:

                  generate another one in the given range which is not in the table yet

                  end if

                  Because if there is duplicate random number generated in the table, we will be in trouble.

                   

                  Thanks.

                  • 7. Re: Unique Random number
                    Nimish Garg

                    It will generate unique number in its execution. But there is no check for existing data as we are already deleting existing records from table.

                    • 8. Re: Unique Random number
                      user12050217

                      No, no. Although in the procedure you have said delete from rnd; but I am asking that will this procedure always generate unique numbers please; i.e. if I says :

                      alter table rnd add constraint unqrnd unique (rnd);

                      and I shall never get :

                      ORA-00001: unique constraint (SCOTT.UNQRND) violated and fail the procedure execution ?

                      • 9. Re: Unique Random number
                        Nimish Garg

                        Yes it will...

                        • 10. Re: Unique Random number
                          user12050217

                          Nimish Garg wrote:

                           

                          Yes it will...

                          Ok.  Can you please tell me, at which stage/part it is checking for uniqueness.  I just wish to know and learn about its uniqueness and randomization please.

                          • 11. Re: Unique Random number
                            Karthick_Arp

                            I don't understand your requirement clearly. If i execute the procedure like this

                             

                            genrand(100, 200)

                             

                            What is your expectation.

                             

                            1. 100 unique number should be generated, correct?

                            2, Should the random numbers be between 100 and 200, or can it be any number? If its between 100 and 200 then its not much of a random number, is it?

                            3. What if the procedure is called with the same input values multiple times? The table may endup with duplicate values, is that OK?

                            • 12. Re: Unique Random number
                              user12050217

                              Thanks for your participation in my request.

                               

                              1.Yes

                              2.Yes, random numbers should only be in the range of 100 to 200.

                              >If its between 100 and 200 then its not much of a random number, is it?

                              Yes, they are good for us as random numbers.

                              3.No, only one user will execute the procedure only once at a time, further data processing will happen upon the rnd table, processing completed and again new processing will happen for other data depending upon the uniqueness and randomization as per values of rnd column of rnd table please.

                              • 13. Re: Unique Random number
                                Roger

                                Random and uniqueness somehow does not fit in one requirement ... just because if it is important to respect a increasing list of numbers that should not be generated, then the generation is no longer random.

                                 

                                So

                                 

                                If it is important that your numbers are random, than forget about the uniqueness.

                                 

                                If it is importand that your numbers are unique, then why do you bother how they are generated?

                                 

                                What is the businesscase anyway?

                                 

                                hth

                                • 14. Re: Unique Random number
                                  Karthick_Arp

                                  >If its between 100 and 200 then its not much of a random number, is it?

                                  Yes, they are good for us as random numbers.

                                   

                                  Why should it be that way. What business does it address. Why cant it be just a sequential value. of 100, 101, 102 etc... What different it makes by being 105, 102, 155 etc?

                                  3.No, only one user will execute the procedure only once at a time, further data processing will happen upon the rnd table, processing completed and again new processing will happen for other data depending upon the uniqueness and randomization as per values of rnd column of rnd table please.

                                   

                                  I don't understand this. So you say your application takes care that same input is not passed to the procedure again?

                                   

                                  Overall I don't get the need for a random number here.

                                  1 2 3 Previous Next