PL/SQL Generating a random number
Author Message
PL/SQL Generating a random number

Hi,
I need to generate a four digit random number within a PL/SQL script.
Does anyone have any ideas on how to accomplish this?

Thank you

Gerald

Tue, 29 Feb 2000 03:00:00 GMT
PL/SQL Generating a random number

Quote:
>Hi,
>    I need to generate a four digit random number within a PL/SQL script.
>Does anyone have any ideas on how to accomplish this?

>Thank you

>Gerald

rand_max(9999) or get_rand_max(9999) from below will do it.

create or replace package random
is
pragma restrict_references( random, WNDS, RNPS );

procedure srand( new_seed in number );

function rand return number;
pragma restrict_references( rand, WNDS  );

procedure get_rand( r OUT number );

function rand_max( n IN number ) return number;
pragma restrict_references( rand_max, WNDS);

procedure get_rand_max( r OUT number, n IN number );

end random;
/

create or replace package body random
is
multiplier      constant number         := 22695477;
increment       constant number         := 1;
"2^32"                constant number                 := 2 ** 32;
"2^16"                constant number         := 2 ** 16;
"0x7fff"      constant number         := 32767;
Seed            number := 1;
--
procedure srand( new_seed in number )
is
begin
Seed := new_seed;
end srand;
--
function rand return number
is
begin
seed := mod( multiplier * seed + increment, "2^32" );
return bitand( seed/"2^16", "0x7fff" );
end rand;
--
procedure get_rand( r OUT number )
is
begin
r := rand;
end get_rand;
--
function rand_max( n IN number ) return number
is
begin
return mod( rand, n ) + 1;
end rand_max;
--
procedure get_rand_max( r OUT number, n IN number )
is
begin
r := rand_max( n );
end get_rand_max;
--
begin
select userenv( 'SESSIONID' ) into seed from dual;
end random;
/

Thomas Kyte

Oracle Government
Bethesda MD