generating (unique) random numbers for a column

From: Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com>
Date: Mon, 28 Apr 2003 17:13:27 GMT
Message-ID: <Xidra.1$yv1.4080@news2.telusplanet.net>

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; Received on Mon Apr 28 2003 - 12:13:27 CDT