1 2 Previous Next 17 Replies Latest reply on Jun 26, 2012 8:01 PM by Etbin

# generate 5 digit unique random number based on year

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
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
CREATE SEQUENCE SEQ_A
MINVALUE 100
MAXVALUE 465
INCREMENT BY 1
CACHE 20
NOORDER
CYCLE ;

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

You can use sequence like this.
• ###### 3. Re: generate 5 digit unique random number based on year
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
Unique AND random at the same time?
http://en.wikipedia.org/wiki/Oxymoron
• ###### 5. Re: generate 5 digit unique random number based on year
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
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
insa wrote:
CREATE SEQUENCE SEQ_A
MINVALUE 100
MAXVALUE 465
INCREMENT BY 1
CACHE 20
NOORDER
CYCLE ;

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

You can use sequence like this.
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
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
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
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
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
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
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
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
Hi,