1 PACKAGE BODY dbms_random AS
2 mem num_array; -- big internal state hidden from the user
3 counter BINARY_INTEGER := 55;-- counter through the results
4 saved_norm NUMBER := NULL; -- unused random normally distributed value
5 need_init BOOLEAN := TRUE; -- do we still need to initialize
6
7
8 -- Seed the random number generator with a binary_integer
9 PROCEDURE seed(val IN BINARY_INTEGER) IS
10 BEGIN
11 seed(TO_CHAR(val));
12 END seed;
13
14
15 -- Seed the random number generator with a string.
16 PROCEDURE seed(val IN VARCHAR2) IS
17 junk VARCHAR2(2000);
18 piece VARCHAR2(20);
19 randval NUMBER;
20 mytemp NUMBER;
21 j BINARY_INTEGER;
22 BEGIN
23 need_init := FALSE;
24 saved_norm := NULL;
25 counter := 0;
26 junk := TO_SINGLE_BYTE(val);
27 FOR i IN 0..54 LOOP
28 piece := SUBSTR(junk,1,19);
29 randval := 0;
30 j := 1;
31
32 -- convert 19 characters to a 38-digit number
33 FOR j IN 1..19 LOOP
34 randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
35 END LOOP;
36
37 -- try to avoid lots of zeros
38 randval := randval*1e-38+i*.01020304050607080910111213141516171819;
39 mem(i) := randval - TRUNC(randval);
40
41 -- we've handled these first 19 characters already; move on
42 junk := SUBSTR(junk,20);
43 END LOOP;
44
45 randval := mem(54);
46 FOR j IN 0..10 LOOP
47 FOR i IN 0..54 LOOP
48
49 -- barrelshift mem(i-1) by 24 digits
50 randval := randval * 1e24;
51 mytemp := TRUNC(randval);
52 randval := (randval - mytemp) + (mytemp * 1e-38);
53
54 -- add it to mem(i)
55 randval := mem(i)+randval;
56 IF (randval >= 1.0) THEN
57 randval := randval - 1.0;
58 END IF;
59
60 -- record the result
61 mem(i) := randval;
62 END LOOP;
63 END LOOP;
64 END seed;
65
66
67 PROCEDURE record_random_number(val IN NUMBER) IS
68 LANGUAGE C
69 NAME "kecrRecordRandomNumber"
70 LIBRARY dbms_workload_capture_lib
71 WITH CONTEXT
72 PARAMETERS
73 ( CONTEXT,
74 val OCINumber );
75
76 FUNCTION replay_random_number RETURN NUMBER IS
77 LANGUAGE C
78 NAME "kecpReplayRemappedRandomNumber"
79 LIBRARY dbms_workload_replay_lib
80 WITH CONTEXT
81 PARAMETERS
82 ( CONTEXT,
83 RETURN INDICATOR );
84
85 -- give values to the user
86 -- Delayed Fibonacci, pilfered from Knuth volume 2
87 FUNCTION value RETURN NUMBER PARALLEL_ENABLE IS
88 randval NUMBER;
89 BEGIN
90
91 randval := replay_random_number(); -- null if not in replay mode
92 IF randval IS NOT NULL THEN
93 RETURN randval;
94 END IF;
95
96 counter := counter + 1;
97 IF counter >= 55 THEN
98
99 -- initialize if needed
100 IF (need_init = TRUE) THEN
101 seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
102 USER || USERENV('SESSIONID'));
103 ELSE
104 -- need to generate 55 more results
105 FOR i IN 0..30 LOOP
106 randval := mem(i+24) + mem(i);
107 IF (randval >= 1.0) THEN
108 randval := randval - 1.0;
109 END IF;
110 mem(i) := randval;
111 END LOOP;
112 FOR i IN 31..54 LOOP
113 randval := mem(i-31) + mem(i);
114 IF (randval >= 1.0) THEN
115 randval := randval - 1.0;
116 END IF;
117 mem(i) := randval;
118 END LOOP;
119 END IF;
120 counter := 0;
121 END IF;
122
123 record_random_number(mem(counter)); -- no-op if not in recording
124
125 RETURN mem(counter);
126 END value;
127
128
129 -- Random 38-digit number between LOW and HIGH.
130 FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER
131 PARALLEL_ENABLE is
132 BEGIN
133 RETURN (value*(high-low))+low;
134 END value;
135
136
137 -- Random numbers in a normal distribution.
138 -- Pilfered from Knuth volume 2.
139 FUNCTION normal RETURN NUMBER PARALLEL_ENABLE is
140 -- 38 decimal places: Mean 0, Variance 1
141 v1 NUMBER;
142 v2 NUMBER;
143 r2 NUMBER;
144 fac NUMBER;
145 BEGIN
146 IF saved_norm is not NULL THEN -- saved from last time
147 v1 := saved_norm; -- to be returned this time
148 saved_norm := NULL;
149 ELSE
150 r2 := 2;
151 -- Find two independent uniform variables
152 WHILE r2 > 1 OR r2 = 0 LOOP
153 v1 := value();
154 v1 := v1 + v1 - 1;
155 v2 := value();
156 v2 := v2 + v2 - 1;
157 r2 := v1*v1 + v2*v2; -- r2 is radius
158 END LOOP; -- 0 < r2 <= 1: in unit circle
159 /* Now derive two independent normally-distributed variables */
160 fac := sqrt(-2*ln(r2)/r2);
161 v1 := v1*fac; -- to be returned this time
162 saved_norm := v2*fac; -- to be saved for next time
163 END IF;
164 RETURN v1;
165 END normal;
166
167
168 -- Random string. Pilfered from Chris Ellis.
169 FUNCTION string (opt char, len NUMBER)
170 RETURN VARCHAR2 PARALLEL_ENABLE is -- string of <len> characters
171 optx char (1) := lower(opt);
172 rng NUMBER;
173 n BINARY_INTEGER;
174 ccs VARCHAR2 (128); -- candidate character subset
175 xstr VARCHAR2 (4000) := NULL;
176 BEGIN
177 IF optx = 'u' THEN -- upper case alpha characters only
178 ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
179 rng := 26;
180 ELSIF optx = 'l' THEN -- lower case alpha characters only
181 ccs := 'abcdefghijklmnopqrstuvwxyz';
182 rng := 26;
183 ELSIF optx = 'a' THEN -- alpha characters only (mixed case)
184 ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ||
185 'abcdefghijklmnopqrstuvwxyz';
186 rng := 52;
187 ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper)
188 ccs := '0123456789' ||
189 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
190 rng := 36;
191 ELSIF optx = 'p' THEN -- any printable char (ASCII subset)
192 ccs := ' !"#$%&''()*+,-./' || '0123456789' || ':;<=>?@' ||
193 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' || '[\]^_`' ||
194 'abcdefghijklmnopqrstuvwxyz' || '{|}~' ;
195 rng := 95;
196 ELSE
197 ccs := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
198 rng := 26; -- default to upper case
199 END IF;
200 FOR i IN 1 .. least(len,4000) LOOP
201 /* Get random integer within specified range */
202 n := TRUNC(rng * value) + 1;
203 /* Append character to string */
204 xstr := xstr || SUBSTR(ccs,n,1);
205 END LOOP;
206 RETURN xstr;
207 END string;
208
209 -- For compatibility with 8.1
210 PROCEDURE initialize(val IN BINARY_INTEGER) IS
211 BEGIN
212 seed(to_char(val));
213 END initialize;
214
215
216 -- For compatibility with 8.1
217 -- Random binary_integer, -power(2,31) <= Random < power(2,31)
218 -- Delayed Fibonacci, pilfered from Knuth volume 2
219 FUNCTION random RETURN BINARY_INTEGER PARALLEL_ENABLE IS
220 BEGIN
221 RETURN TRUNC(Value*4294967296)-2147483648;
222 END random;
223
224
225 -- For compatibility with 8.1
226 PROCEDURE terminate IS
227 BEGIN
228 NULL;
229 END terminate;
230
231 END dbms_random;
[Home] [Help]