You are trying to generate random numbers without replacement. dbms_random
cannot do that; It would have to remember all the numbers it generated to do
that. If you know how many rows you could create a table with that many
rows and an unique constraint and fill that table with values. Then use
that series until you finish. That is make your own list of numbers that
are random and unique.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Glen A Stromquist" <glen_stromquist_at_no.spam.yahoo.com> wrote in message
news:Xidra.1$yv1.4080_at_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 - 21:00:41 CDT
