Howto generate unique random id

[Maciej Miklas]

Hi,

I need to generate unique random integer in range 1-9999999 - the best would be oracle procedure.

Does anyone have an example?


I know how to generate random, or next val from sequence. But I need like next random value from sequence.

[Jeanne Boyarsky]

See this article about Oracle's random package. I can't imagine why you would need a random number, but it's there.

[Maciej Miklas]

because I need to use it as a PIN for the user.

I know this article - but generated numbers are not guaranteed to be unique.

I am using hibernate in cluster - the common place is DB - I need to lock table or generate my PIN direct in oracle.

[Jan Cumps]

the common place is DB - I need to lock table or generate my PIN direct in oracle.

Or use another shared service.
I don't know that "the common place" is the right driver to decide to do it in the database. What if one decides to cluster the database?

A driver to do it in the database is the fact that the number has to be unique. This means you will have to store all numbers that have already been handed out.

If you decide to do it in the database, you can:

A: Create a table to hold the numbers, with a unique constraint on that field.
B: Generate a random number wih your favorite random number algorithm.
C: Insert that number in the previous table. If success: return the number; if failure, loop to B.

(I even think that this mechanism works when concurrent requests would by accident try to return the same number, because one of the two attempts would fail in step C. But I like to be challenged.)

[edit note to self: why is it that you don't see silly mistakes in preview, but they jump straight into your eye after submitting?]

[Maciej Miklas]

yes - this is what I need. I was hoping to get some code example from someone

[Jan Cumps]

Maciej Miklas wrote:yes - this is what I need. I was hoping to get some code example from someone

From that article mentionned by Jeanne.
See example Generating a n digit random number. Combine with my mechanism described above to reject duplicates.

Tell us if you find a better solution.

[Mike Simmons]

In Java, there are two common solutions:

(1) Use a HashSet to store all the previously generated numbers in. Each time you need a new number, generate one randomly, and check if it's in the Set of previously-generated numbers. If it it, try again. Repeat until you get a new number - and make sure that new number gets stored in the Set, as well.

(2) Create a List of Integer objects, numbered 0, 1, 2, 3, 4... n-1. (Or whatever range you want.) Use Collections.shuffle(). Now just read the list elements one after another - these are your randomly-generated numbers, with no repeats.

Both of these solutions can be modifed to work in PL-SQL using a table in place of the Set or List. The first is basically what Jan has been describing. It's relatively easy; its only downside is that it becomes less efficient as you get closer to the end, and it becomes harder and harder to find a new random value that hasn't already been used.

For the second technique, you can look at the source code of Collections.shuffle() to see how it works; it's fairly simple. This can be further adapted to avoid the need to pre-shuffle the entire list, instead picking just one new random element each time. You can also avoid the need to initialize the entire list beforehand by simply assuming that any row not already present in the table has a value equal to its position, e.g. the element at index 42 would have value 42.

Here's a very lazy Java implementation that uses these techniques and even omits half of each swap operation, on the assumption that there's no intent to re-read old elements of the sequence:

import java.util.Random;

public class LazyShuffler {

  private final int[] array;
  private int pos;
  private final Random rand = new Random();

  public LazyShuffler(int size) { array = new int[size]; }

  public boolean hasNext() { return pos < array.length; }

  public int next() {
    int i = pos + rand.nextInt(array.length - pos);
    int n = read(i);
    if (i != pos)
      array[i] = read(pos) + 1;
    pos++;
    return n;
  }

  private int read(int i) {
    int x = array[i];
    return x == 0 ? i : x - 1;
  }

  public static void main(String[] args) {
    LazyShuffler ls = new LazyShuffler(10);
    while (ls.hasNext())
      System.out.println(ls.next());
  }
}

[Jeanne Boyarsky]

Maciej Miklas wrote:because I need to use it as a PIN for the user.

Why do PIN numbers need to be unique? In fact, it's more secure if they aren't. If I can't pick a PIN because someone else has it, you've just told me something about the security of your system. Now I just have to find the user it goes with. Which a hacker could brute force.

[Mike Simmons]

Well, I don't know either why pins might need to be unique - that's a good question. But in the system posited, it doesn't sound like anyone has the option of choosing a pin either. They're assigned randomly (with the constraint of non-repetition). So if I get assigned a pin of 8376504, what does that tell me about other people's pins? Well, it tells me that no one else has a pin of 8376504. Great - that's one down, 9999998 to go. Doesn't seem very useful to me. Unless, perhaps, I find a separate security bug that allows me to register millions of different accounts, seizing a substantial portion of the available pinspace. Yes, that would be a problem. But I'd say it's a problem because some hacker has the ability to register millions of bogus accounts. Not because the pins are unique.

Still, I'd be interested to know why pins need to be unique. It seems kind of, well, useless. Is there a benefit I'm missing?

[Maciej Miklas]

correct - but this is not my idea.... but requirement and I need to implement it

[Jeanne Boyarsky]

Mike Simmons wrote:Well, I don't know either why pins might need to be unique - that's a good question. But in the system posited, it doesn't sound like anyone has the option of choosing a pin either.

Right. If you can't choose one, this doesn't help. If you can, the security hole I described comes into play.