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>


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

Original text of this message