Re: generating (unique) random numbers for a column

On Mon, 28 Apr 2003 17:13:27 +0000, Glen A Stromquist wrote:

> I am trying to populate a column in a table with random numbers with roughly
> the same range as rows in the table, I want these unique so have added the
> constraint. I have a simple trigger that generates a random number before
> insert so when I load the table with sqlldr the numbers are generated.
>
> My problem is that 50 or so of the 500 records get left out because the
> constraint is violated when the dbms_random.value(<range>) tries to pick a
> number it has already generated.
>
> the trigger is as follows:
>
> CREATE OR REPLACE TRIGGER "schema"."GEN_RAND" BEFORE INSERT OR
> UPDATE OF "FIRST_NAME", "LAST_NAME", "ENTERED", "RANDOM" ON
> "COMPANY_LIST"
> FOR EACH ROW
> declare
> x number;
> begin
> x :=dbms_random.value(1,600);
> :new.random := x;
> end;

Well, random means random, not necessarily unique. Have a sequence instead or make an Erathosten's sieve program and pick only the prime numbers. Users would be very happy with a message like:
Enter any 12 digits prime number to continue....

-- 
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds