This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jun 26, 2012 1:01 PM by Etbin RSS

generate 5 digit unique random number based on year

869556 Newbie
Currently Being Moderated
hi
I need to generate 5 digit unique random number based on year. if year change then duplicate random number allow.
Thanks
  • 1. Re: generate 5 digit unique random number based on year
    hitgon Expert
    Currently Being Moderated
    Refer the SYS_GUID
    http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions153.htm
    http://sqlfascination.com/2012/01/22/oracle-duplicate-guid-values-being-returned-from-sys_guid-when-run-in-parallel/

    Edited by: hitgon on Jun 26, 2012 4:58 PM
  • 2. Re: generate 5 digit unique random number based on year
    INRi Explorer
    Currently Being Moderated
    CREATE SEQUENCE SEQ_A
    MINVALUE 100
    MAXVALUE 465
    INCREMENT BY 1
    START WITH 100
    CACHE 20
    NOORDER
    CYCLE ;

    select SEQ_A.NEXTVAL||to_char(sysdate,'yy') from dual;

    You can use sequence like this.
    Is it helpful for you???
  • 3. Re: generate 5 digit unique random number based on year
    869556 Newbie
    Currently Being Moderated
    Thanks for your reply.
    Actually i need random number. So user cannot identified how the number is generated sequence.
    Thanks
  • 4. Re: generate 5 digit unique random number based on year
    Hoek Guru
    Currently Being Moderated
    Unique AND random at the same time?
    http://en.wikipedia.org/wiki/Oxymoron
    which inevitably leads to:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:831827028200
  • 5. Re: generate 5 digit unique random number based on year
    BluShadow Guru Moderator
    Currently Being Moderated
    Shiv wrote:
    hi
    I need to generate 5 digit unique random number
    There is no such thing as a unique random number.

    You can have random numbers.
    You can have unique numbers.
    But you cannot have both. Think about it. The definition of random means that the same number may appear more than once.
  • 6. Re: generate 5 digit unique random number based on year
    Karthick_Arp Guru
    Currently Being Moderated
    Shiv wrote:
    hi
    I need to generate 5 digit unique random number based on year. if year change then duplicate random number allow.
    Thanks
    Whats the point in generating Unique number in Random order? Does not make sense to me..
  • 7. Re: generate 5 digit unique random number based on year
    jeneesh Guru
    Currently Being Moderated
    insa wrote:
    CREATE SEQUENCE SEQ_A
    MINVALUE 100
    MAXVALUE 465
    INCREMENT BY 1
    START WITH 100
    CACHE 20
    NOORDER
    CYCLE ;

    select SEQ_A.NEXTVAL||to_char(sysdate,'yy') from dual;

    You can use sequence like this.
    Is it helpful for you???
    A sequence cannot be treated as a random number.

    @OP => Could you please explain your actual buisness requirement? The one liner requiremnt you have given seems to be very strange as a set of random numbers cannot be unique. For example suppose you have implented a logic and generated 99999 values, the next number cannot be random. It is known.

    If it is that mandatory to have this logic implemented, some questions have to be answered

    1. How many unique values you would require in an year or what is the number of transactions daily?
    2. Why do you want to relate it to year?

    Edited by: jeneesh on Jun 26, 2012 3:40 PM

    Too late to post...
  • 8. Re: generate 5 digit unique random number based on year
    869556 Newbie
    Currently Being Moderated
    If the current year is "2012" then random number 88888 is unique for the year "2012" but it can be duplicate in year "2013".

    Thanks
  • 9. Re: generate 5 digit unique random number based on year
    BluShadow Guru Moderator
    Currently Being Moderated
    The only way you will achieve something like that is to generate a random number and then check to see if it's already been used for that year and if it has, then generate a new one to check and so on until you find one that hasn't been used. In a multi-user environment this is not an appropriate method as, between you checking if it's been used before that year and finding it has not, another user could have also found the same and created a record for it, so you will end up with duplicates. It would only work for a single user environment, hence that method doesn't scale well at all.

    Explain clearly what you are trying to achieve and why? What logical reason could you have for wanting unique random numbers per year?
  • 10. Re: generate 5 digit unique random number based on year
    Karthick_Arp Guru
    Currently Being Moderated
    Shiv wrote:
    If the current year is "2012" then random number 88888 is unique for the year "2012" but it can be duplicate in year "2013".

    Thanks
    Why should it be random. Why cant it be like
    201200001
    201200002
    201200003
    ...
  • 11. Re: generate 5 digit unique random number based on year
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    If you really want the numbers in random order, then you can create a table like this:
    CREATE TABLE     my_ids     AS
    SELECT  LEVEL                              AS sequential_id
    ,     ROW_NUMBER () OVER (ORDER BY dbms_random.value)     AS random_id
    FROM     dual
    CONNECT BY     LEVEL <= 10     -- Or any number
    ;
    Use a sequence to get the sequential_id, then store the corresponding random_id.
  • 12. Re: generate 5 digit unique random number based on year
    BluShadow Guru Moderator
    Currently Being Moderated
    Or, using a sequence, generate hash values...
    SQL> ed
    Wrote file afiedt.buf
    
      1  select rownum, dbms_crypto.hash(utl_raw.cast_to_raw(rownum),3) as hashval
      2  from dual
      3* connect by rownum <= 10
    SQL> /
    
        ROWNUM HASHVAL
    ---------- ----------------------------------------
             1 356A192B7913B04C54574D18C28D46E6395428AB
             2 DA4B9237BACCCDF19C0760CAB7AEC4A8359010B0
             3 77DE68DAECD823BABBB58EDB1C8E14D7106E83BB
             4 1B6453892473A467D07372D45EB05ABC2031647A
             5 AC3478D69A3C81FA62E60F5C3696165A4E5E6AC4
             6 C1DFD96EEA8CC2B62785275BCA38AC261256E278
             7 902BA3CDA1883801594B6E1B452790CC53948FDA
             8 FE5DBBCEA5CE7E2988B8C69BCFDFDE8904AABC1F
             9 0ADE7C2CF97F75D009975F4D720D1FA6C19F4897
            10 B1D5781111D84F7B3FE45A0852E59758CD7A87E5
    
    10 rows selected.
    which certainly don't appear as sequential values, so give the appearance of being random, and they are unique, though the result is obviously a hexadecimal number rather than decimal.

    Oh, and it's not 5 digits either, but hey you can't have everything.
  • 13. Re: generate 5 digit unique random number based on year
    BluShadow Guru Moderator
    Currently Being Moderated
    Frank Kulash wrote:
    Hi,

    If you really want the numbers in random order, then you can create a table like this:
    CREATE TABLE     my_ids     AS
    SELECT  LEVEL                              AS sequential_id
    ,     ROW_NUMBER () OVER (ORDER BY dbms_random.value)     AS random_id
    FROM     dual
    CONNECT BY     LEVEL <= 10     -- Or any number
    ;
    Use a sequence to get the sequential_id, then store the corresponding random_id.
    Then the randomness will repeat itself each year?? ;)
  • 14. Re: generate 5 digit unique random number based on year
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    BluShadow wrote:
    ... Then the randomness will repeat itself each year?? ;)
    If that's what 's wanted, it can. Some people seem intent on repeating the same random behavior over and over. (Hoek may correct me, and point out that if it's the same behavior, then it isn't really random.)

    But, seriously, folks,, the random_id column could be re-populated every year. Depending on requirements, it might be necessary to have additional columns, such as prior_year_random_id and/or next_year_random_id. You could also add a year column, and have multiple rows with the same sequential_id, but different years.
1 2 Previous Next

Legend

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