This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 29, 2009 6:10 AM by user601042 RSS

random  number generation

user10447332 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    Everything is fine but you dint answer the question. OP wants to generate unique random number.
  • 3. Re: random  number generation
    639997 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    would using a sequence work in your case?

    setting the min and max value and so on.
  • 7. Re: random  number generation
    554938 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    Even when it is a unique random number, you can always make a wild guess...
  • 9. Re: random  number generation
    554938 Journeyer
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    Check this thread

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:831827028200
  • 11. Re: random  number generation
    21205 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points