1 2 Previous Next 17 Replies Latest reply: Jan 29, 2009 8:10 AM by user601042 RSS

    random  number generation

    user10447332
      we are doing telecom domain.
      we have requirement as we have to create random number of length 10 digit .that number should not be repeated.
      this is similar like vodafone recharge card.this card has numbers to recharge mobile talk time we are using that 10 digitnumber.how to create taht 10 digit number.if we use random package there may be chance of repeating the number because the number is randomly generated.how to create taht number.
        • 1. Re: random  number generation
          639997
          RANDOM VALUES

          --To select A Random Value
          SELECT DBMS_RANDOM.VALUE
          FROM DUAL;

          VALUE
          ----------
          .565084617

          --To generate a whole number as random
          SELECT TRUNC (DBMS_RANDOM.VALUE (0, 100))
          FROM DUAL
          TRUNC(DBMS_RANDOM.VALUE(0,100))
          -------------------------------
          25
          --To generate random text strings
          SELECT DBMS_RANDOM.STRING ('S', 20)
          FROM DUAL;
          DBMS_RANDOM.STRING('S',8)
          -----------------------------------------------------------------
          WJSDCGUV
          --Upper String-U
          SELECT DBMS_RANDOM.STRING ('U', 20)
          FROM DUAL;
          DBMS_RANDOM.STRING('U',20)
          ---------------------------------------------------------------
          JKWYVTEDDMRGQWMLDISE
          --Lower String
          SELECT DBMS_RANDOM.STRING ('L', 20)
          FROM DUAL;
          DBMS_RANDOM.STRING('L',20)
          -------------------------------------------------
          tlzgnhjmrgpmlmklhqgy
          --Date
          SELECT TO_CHAR (TO_DATE ('01/01/08', 'mm/dd/rr'), 'J')
          FROM DUAL;
          TO_CHAR
          -------
          2454467
          --conversion
          SELECT TO_DATE (TRUNC (DBMS_RANDOM.VALUE (2454467,2454467+ 364)), 'J')
          FROM DUAL;
          TO_DATE(T
          ---------
          22-AUG-08
          • 2. Re: random  number generation
            _Karthick_
            Everything is fine but you dint answer the question. OP wants to generate unique random number.
            • 3. Re: random  number generation
              639997
              E.g.: Generating a 12 digit random number.
              SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

              NUM
              _____________
              175055628780
              /
              declare
              2 l_num number;
              3 begin
              4 l_num := dbms_random.random;
              5 dbms_output.put_line(l_num);
              6 dbms_random.seed('caps testing 67890');
              7 l_num := dbms_random.random;
              8 dbms_output.put_line(l_num);
              9 end;
              10 /
              483791552
              478774329

              PL/SQL procedure successfully completed.
              • 4. Re: random  number generation
                647399
                I read the documentation on the random function recently and i don't recall reading anything in regards to preventing duplicates.

                The only way i can see is performing a check on what the random procedure returns.
                • 5. Re: random  number generation
                  554938
                  Generate the numbers using random package and store the numbers in a unique column will do the trick.

                  If a number will be present in table column already , it will through an exception. Handle it and continue the process.
                  • 6. Re: random  number generation
                    647399
                    would using a sequence work in your case?

                    setting the min and max value and so on.
                    • 7. Re: random  number generation
                      554938
                      Hi ,

                      Sequence will not help OP in his problem because the numbers should not be in sequence, otherwise you can by a recharge voucher , make a wild guess suppose add 10 , and do the recharge again with new number.

                      Regards

                      Rajneesh
                      • 8. Re: random  number generation
                        21205
                        Even when it is a unique random number, you can always make a wild guess...
                        • 9. Re: random  number generation
                          554938
                          Hi Alex,

                          agree with your point , but the probability of the same is very less.

                          OP : The recharge voucher number is not only a random number , but it is a combination of some other things as well including a random number, its better if you can clarify with business team.
                          • 10. Re: random  number generation
                            _Karthick_
                            Check this thread

                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:831827028200
                            • 11. Re: random  number generation
                              21205
                              Indeed,..

                              I believe the best way would be to use a sequence,.. another option would be to do it the way Rajneesh mentioned before with a table - but this doesn't scale well.

                              here is a code sample
                              SQL> create table t
                                2  as
                                3  select rownum id
                                4       , cast (null as varchar2(1)) used 
                                5    from all_objects
                                6   where rownum <= 5
                                7  /
                              
                              Table created.
                              
                              SQL> 
                              SQL> 
                              SQL> alter table t
                                2    add constraint tpk primary key (id)
                                3  /
                              
                              Table altered.
                              
                              SQL> 
                              SQL> alter table t
                                2    add constraint used_chk check (used in ('Y'))
                                3  /
                              
                              Table altered.
                              
                              SQL> 
                              SQL> create or replace 
                                2  function f return number
                                3  is
                                4    pragma autonomous_transaction;
                                5    retval number;
                                6  begin
                                7    update t
                                8       set used = 'Y'
                                9     where id = (select id
                               10                  from (select id
                               11                            , used 
                               12                         from t
                               13                        where used is null
                               14                        order by dbms_random.value
                               15                      )
                               16                  where rownum = 1
                               17               )
                               18    returning id into retval;
                               19    if sql%rowcount != 1
                               20    then   
                               21      raise program_error;
                               22    end if;
                               23    commit;
                               24    return retval;
                               25  end f;  
                               26  /
                              
                              Function created.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              3
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              1
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              5
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              4
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              2
                              
                              PL/SQL procedure successfully completed.
                              
                              SQL> begin
                                2      dbms_output.put_line (f);
                                3  end;
                                4  /
                              begin
                              *
                              ERROR at line 1:
                              ORA-06501: PL/SQL: program error
                              ORA-06512: at "SENUIAL.F", line 20
                              ORA-06512: at line 2
                              
                              
                              SQL> 
                              BUT: read this:
                              http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1423493900346328973
                              and use a sequence instead!

                              Edited by: Alex Nuijten on Jan 28, 2009 9:02 AM
                              changed line 19 because of the forum...
                              • 12. Re: random  number generation
                                647399
                                How bout this?

                                Use a sequence to generate a 6 digit number.
                                Use random to generate a 4 digit number.

                                Concatenate the two numbers.

                                Insert into a unique column for verification.


                                This will make it less likely they will be able to guess the number generated.

                                Edited by: Datalus on Jan 29, 2009 11:37 AM
                                • 13. Re: random  number generation
                                  user10447332
                                  to create random unique number i written the below procedure.in real time we have millions of records.peformance wise is this procedure will work.
                                  CREATE OR REPLACE procedure testing
                                  as
                                  rnum number(10);
                                  nmin number:=1000000000;
                                  nmax number:=9999999999;
                                  amax number;
                                  c number;
                                  myexception exception;
                                  begin
                                  select count(voucher_no) into amax from test;
                                  if amax>=(nmax-nmin+1) then
                                  raise myexception;
                                  end if;
                                  --select count(no) into amax from test;
                                  <<comehere>>
                                  rnum:=ROUND (DBMS_RANDOM.VALUE(nmin,nmax ));
                                  dbms_output.put_line(rnum);
                                  select count(*) into c from test where no=rnum;
                                  if c>0 then
                                  goto comehere;
                                  else
                                  insert into test values(rnum);
                                  end if;

                                  exception
                                  when myexception then
                                  raise_application_error (-20991, 'all numbers are used ');
                                  end;
                                  /
                                  • 14. Re: random  number generation
                                    21205
                                    As you are not showing a complete example, I am assuming you have a table like this
                                    create table test
                                    (voucher_no number primary key
                                    )
                                    and your procedure, but I changed the upper and lower limits just to make a point:
                                    CREATE OR REPLACE procedure testing
                                    as
                                    rnum number(10);
                                    nmin number:=1 ; -- 1000000000;
                                    nmax number:=9; -- 9999999999;
                                    amax number;
                                    c number;
                                    myexception exception;
                                    begin
                                    select count(voucher_no) into amax from test;
                                    if amax>=(nmax-nmin+1) then 
                                    raise myexception;
                                    end if;
                                    --select count(no) into amax from test;
                                    <<comehere>>
                                    rnum:=ROUND (DBMS_RANDOM.VALUE(nmin,nmax ));
                                    dbms_output.put_line(rnum);
                                    select count(*) into c from test where voucher_no=rnum;
                                    if c>0 then 
                                    goto comehere;
                                    else 
                                    insert into test values(rnum);
                                    end if;
                                    exception
                                    when myexception then
                                    raise_application_error (-20991, 'all numbers are used ');
                                    end;
                                    How can you say the performance will work?... Have you considered this scenario:
                                    session one executes the procedure and will get voucher no "4" -- no commit or rollback; transaction still open
                                    session two executes the procedure and will get voucher no "4" - because the first session is still open your first SELECT COUNT will yield zero (assuming isolation level read committed)

                                    Now session two is waiting for session one to decide whether it is going to commit or rollback...

                                    ... or are you leaving something out of your scenario?
                                    1 2 Previous Next