Re: Random numbers in PL/SQL.

From: Jens Mayer <jmayer_at_ratundtat.com>
Date: Tue, 23 Nov 1999 16:40:44 GMT
Message-ID: <383ac24f.23908168@news.space.net>

On Mon, 22 Nov 1999 09:45:07 -0500, "Eugene Firyago" <efiryago_at_bisys.com> wrote:

>In Oracle8i: DBMS_RANDOM package. In earlier versions you have to
>programmatically implement one of math methods to generate random numbers
>using PL/SQL.
>
>Jan Ebbe Jensen <janebbe_at_post.tele.dk> wrote in message
>news:819kgt$ml3$1_at_news.inet.tele.dk...
>> How do I generate a random number in PL/SQL?
>>
>>
>
>

Hi Eugene,

i had the same problem some days ago. Use this:

create or replace package pg_random
is

   procedure srand(new_seed in number);    procedure get_rand(r OUT number);
   procedure get_rand_max(r OUT number, n IN number);    function rand return number;
   function rand_max(n IN number) return number;    function rand_string(ssiz IN number) return varchar2;    function smaller(x IN number, y IN number) return number;

   pragma restrict_references(rand, WNDS);
   pragma restrict_references(rand_max, WNDS);
   pragma restrict_references(pg_random, WNDS, RNPS);
   pragma restrict_references(rand_string, WNDS);
   pragma restrict_references(smaller, WNDS);

end pg_random;
/
create or replace package body pg_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;

   function smaller(x IN number, y IN number) return number is    begin

        if x <= y then
            return x;
        else
            return y;
        end if;

   end smaller;

   function rand_string(ssiz IN number) return varchar2 is

     i      number;
     m      number;
     c      char;
     result varchar2(2000) := '';
   begin
        m := smaller(ssiz,2000);
        for i in 1..m loop
            c :=
substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
            result := result || c;
        end loop;
        return result;

   end rand_string;

   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 pg_random;
/

Use it like

-> Random Number between 1 and 10
select pg_random.rand_max(10) from dual;

-> Random string, length of 10 characters select pg_random.rand_string(10) from dual;

I got it from this newsgroup too, so i am not the author of this code...

Hope this helps

Jens

-
Jens Mayer
Rat & Tat GmbH
Hamburg, Germany Received on Tue Nov 23 1999 - 10:40:44 CST