2 ------------------------------------------------------------------------------
3 URL: http://www.orafaq.com/scripts/plsql/random.txt
5 Purpose: Random number/ string generator package
7 Original: http://www.orafaq.org/scripts/sql/random.txt
9 19990908 Phil Rand <prand@spu.edu> Added functions rand_string(), smaller().
10 ------------------------------------------------------------------------------
13 create or replace package random
15 procedure srand(new_seed in number);
16 procedure get_rand(r OUT number);
17 procedure get_rand_max(r OUT number, n IN number);
18 function rand return number;
19 function rand_max(n IN number) return number;
20 function rand_string(ssiz IN number) return varchar2;
21 function smaller(x IN number, y IN number) return number;
22 pragma restrict_references(rand, WNDS);
23 pragma restrict_references(rand_max, WNDS);
24 pragma restrict_references(random, WNDS, RNPS);
25 pragma restrict_references(rand_string, WNDS);
26 pragma restrict_references(smaller, WNDS);
30 create or replace package body random
32 multiplier constant number := 22695477;
33 increment constant number := 1;
34 "2^32" constant number := 2 ** 32;
35 "2^16" constant number := 2 ** 16;
36 "0x7fff" constant number := 32767;
39 function smaller(x IN number, y IN number) return number is
48 function rand_string(ssiz IN number) return varchar2 is
52 result varchar2(2000) := '';
54 m := smaller(ssiz,2000);
56 c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
57 result := result || c;
62 procedure srand(new_seed in number) is
67 function rand return number is
69 Seed := mod(multiplier * Seed + increment, "2^32");
70 return bitand(Seed/"2^16", "0x7fff");
73 procedure get_rand(r OUT number) is
78 function rand_max(n IN number) return number is
80 return mod(rand, n) + 1;
83 procedure get_rand_max(r OUT number, n IN number) is
89 select userenv('SESSIONID')
96 select random.rand_max(10) from dual;
97 select random.rand_max(10) from dual;
98 select random.rand_string(20) from dual;
99 select random.rand_string(20) from dual;