Re: dbms_random in trigger
From: Glen A Stromquist <glen_stromquist_at_no.spam.yahoo.com>
Date: Mon, 28 Apr 2003 16:58:08 GMT
Message-ID: <A4dra.59725$4P1.4208434_at_news2.telusplanet.net>
>
>
> dbms_rand.initialize is a "random" seed start.
> Try a dbms_rand.initialize(0), and generate random numbers.
> Then, wait a while, and redo. See any differences?
>
> Use dbms_random.initialize(to_number(to_char(sysdate, 'miss')));
>
> So there's nothing with initialize that allows for a range.
> Check dbms_rand.value instead - it has a lower and upper limit:
> dbms_rand.value(lower, upper)
>
> declare
> x number;
> begin
> dbms_random.initialize(to_number(to_char(sysdate, 'miss')));
> x :=dbms_random.value(1,3000); -- range defined from 1 to 3000
> :new.random := x;
> end;
Date: Mon, 28 Apr 2003 16:58:08 GMT
Message-ID: <A4dra.59725$4P1.4208434_at_news2.telusplanet.net>
Frank wrote:
> Glen A Stromquist wrote:
>
>> I have created a table with a field called "random' that will hold a >> random number that will be generated before insert via a simple >> trigger which consists of: >> ======================== >> declare >> x number; >> begin >> x :=dbms_random.random; >> :new.random := x; >> end; >> ======================= >> >> This works ok, but I'd like to limit the random numbers to the same >> range as rows in the table, and I'm not sure how to use the >> dbms_rand.initialize to accomplish this.
>
>
> dbms_rand.initialize is a "random" seed start.
> Try a dbms_rand.initialize(0), and generate random numbers.
> Then, wait a while, and redo. See any differences?
>
> Use dbms_random.initialize(to_number(to_char(sysdate, 'miss')));
>
> So there's nothing with initialize that allows for a range.
> Check dbms_rand.value instead - it has a lower and upper limit:
> dbms_rand.value(lower, upper)
>
> declare
> x number;
> begin
> dbms_random.initialize(to_number(to_char(sysdate, 'miss')));
> x :=dbms_random.value(1,3000); -- range defined from 1 to 3000
> :new.random := x;
> end;
[Quoted] Thanks Frank
using the dbms_random.value worked, but now my problem is that if I restrict it to roughly the number of rows in the table, the unique constraint I set up for my "random" column gets violated when importing from sqlldr and records are left out, so I have to figure a way around this... Received on Mon Apr 28 2003 - 18:58:08 CEST