Randomizer Package Specfiication
CREATE OR REPLACE PACKAGE randomizer
/*
| Overview: Simple API to generate collections of random values, unique if desired.
|
| Author: Steven Feuerstein
*/
IS
TYPE integer_aat IS TABLE OF PLS_INTEGER
INDEX BY PLS_INTEGER;
TYPE date_aat IS TABLE OF DATE
INDEX BY PLS_INTEGER;
SUBTYPE maxvarchar2 IS VARCHAR2 (32767);
TYPE maxvarchar2_aat IS TABLE OF maxvarchar2
INDEX BY PLS_INTEGER;
TYPE maxvarchar2_by_string_aat IS TABLE OF maxvarchar2
INDEX BY maxvarchar2;
/* String types based on DBMS_RANDOM.STRING:
u - uppercase
l - lowercase
a - mixed case
x - mix of uppercase and digits
p - any printable character
*/
FUNCTION random_strings (
count_in IN PLS_INTEGER DEFAULT 100
, min_length_in IN PLS_INTEGER DEFAULT 1
, max_length_in IN PLS_INTEGER DEFAULT 100
, string_type_in IN VARCHAR2
DEFAULT NULL /* any printable character */
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN maxvarchar2_aat;
FUNCTION random_integers (
count_in IN PLS_INTEGER DEFAULT 100
, min_value_in IN PLS_INTEGER DEFAULT 1
, max_value_in IN PLS_INTEGER DEFAULT 1000
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN integer_aat;
FUNCTION random_dates (
count_in IN PLS_INTEGER DEFAULT 100
, min_value_in IN DATE DEFAULT SYSDATE - 500
, max_value_in IN DATE DEFAULT SYSDATE + 500
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN date_aat;
PROCEDURE random_verifier (
count_in IN PLS_INTEGER DEFAULT 100
, min_length_in IN PLS_INTEGER DEFAULT 1
, max_length_in IN PLS_INTEGER DEFAULT 100
, string_type_in IN VARCHAR2 DEFAULT NULL
, min_integer_in IN PLS_INTEGER DEFAULT 1
, max_integer_in IN PLS_INTEGER DEFAULT 1000
, min_date_in IN DATE DEFAULT SYSDATE - 500
, max_date_in IN DATE DEFAULT SYSDATE + 500
);
END randomizer; Package created.
Randomizer Package Body
CREATE OR REPLACE PACKAGE BODY randomizer
/*
| Overview: generates collections of random values
|
| Author: Steven Feuerstein
*/
IS
PROCEDURE span_assert (count_in IN PLS_INTEGER, span_in IN PLS_INTEGER)
IS
BEGIN
IF count_in > span_in
THEN
raise_application_error
(-20000
, 'Random generation error: you have requested '
|| count_in
|| ' distinct random values, but your min-max specification allows for only '
|| span_in
|| ' distinct values.'
);
END IF;
END span_assert;
FUNCTION random_strings (
count_in IN PLS_INTEGER DEFAULT 100
, min_length_in IN PLS_INTEGER DEFAULT 1
, max_length_in IN PLS_INTEGER DEFAULT 100
, string_type_in IN VARCHAR2 DEFAULT NULL
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN maxvarchar2_aat
IS
l_value maxvarchar2;
l_values maxvarchar2_aat;
l_used maxvarchar2_by_string_aat;
l_hit_count PLS_INTEGER DEFAULT 0;
BEGIN
-- span_assert (count_in, max_length_in - min_length_in);
WHILE (l_values.COUNT < count_in)
LOOP
l_value :=
DBMS_RANDOM.STRING (string_type_in
, DBMS_RANDOM.VALUE (min_length_in
, max_length_in
)
);
IF distinct_values_in
THEN
IF l_used.EXISTS (l_value)
THEN
l_hit_count := l_hit_count + 1;
IF l_hit_count >= count_in * 5
THEN
raise_application_error
(-20000
, 'Random generation error: Unable to generate '
|| count_in
|| ' distinct strings with min and max lengths '
|| min_length_in
|| ' and '
|| max_length_in
|| '.'
);
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
l_used (l_value) := l_value;
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
END IF;
END LOOP;
RETURN l_values;
END random_strings;
FUNCTION random_integers (
count_in IN PLS_INTEGER DEFAULT 100
, min_value_in IN PLS_INTEGER DEFAULT 1
, max_value_in IN PLS_INTEGER DEFAULT 1000
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN integer_aat
IS
l_value PLS_INTEGER;
l_values integer_aat;
l_used maxvarchar2_by_string_aat;
l_hit_count PLS_INTEGER DEFAULT 0;
BEGIN
span_assert (count_in, max_value_in - min_value_in + 1);
WHILE (l_values.COUNT < count_in)
LOOP
l_value := DBMS_RANDOM.VALUE (min_value_in, max_value_in);
IF distinct_values_in
THEN
IF l_used.EXISTS (l_value)
THEN
l_hit_count := l_hit_count + 1;
IF l_hit_count >= count_in * 5
THEN
raise_application_error
(-20000
, 'Random generation error: Unable to generate '
|| count_in
|| ' distinct integers with min and max values '
|| min_value_in
|| ' and '
|| max_value_in
|| '.'
);
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
l_used (l_value) := l_value;
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
END IF;
END LOOP;
RETURN l_values;
END random_integers;
FUNCTION random_dates (
count_in IN PLS_INTEGER DEFAULT 100
, min_value_in IN DATE DEFAULT SYSDATE - 500
, max_value_in IN DATE DEFAULT SYSDATE + 500
, distinct_values_in IN BOOLEAN DEFAULT TRUE
)
RETURN date_aat
IS
l_values date_aat;
l_date_diff NUMBER := max_value_in - min_value_in;
l_value DATE;
l_used maxvarchar2_by_string_aat;
l_hit_count PLS_INTEGER DEFAULT 0;
BEGIN
span_assert (count_in, max_value_in - min_value_in + 1);
WHILE (l_values.COUNT < count_in)
LOOP
l_value := min_value_in + DBMS_RANDOM.VALUE (1, l_date_diff);
IF distinct_values_in
THEN
IF l_used.EXISTS (TO_CHAR (l_value, 'YYYYMMDDHH24MISS'))
THEN
l_hit_count := l_hit_count + 1;
IF l_hit_count >= count_in * 5
THEN
raise_application_error
(-20000
, 'Random generation error: Unable to generate '
|| count_in
|| ' distinct dates with min and max values '
|| min_value_in
|| ' and '
|| max_value_in
|| '.'
);
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
l_used (TO_CHAR (l_value, 'YYYYMMDDHH24MISS')) := l_value;
END IF;
ELSE
/* Add a new one */
l_values (l_values.COUNT + 1) := l_value;
END IF;
END LOOP;
RETURN l_values;
END random_dates;
PROCEDURE random_verifier (
count_in IN PLS_INTEGER DEFAULT 100
, min_length_in IN PLS_INTEGER DEFAULT 1
, max_length_in IN PLS_INTEGER DEFAULT 100
, string_type_in IN VARCHAR2 DEFAULT NULL
, min_integer_in IN PLS_INTEGER DEFAULT 1
, max_integer_in IN PLS_INTEGER DEFAULT 1000
, min_date_in IN DATE DEFAULT SYSDATE - 500
, max_date_in IN DATE DEFAULT SYSDATE + 500
)
IS
l_strings maxvarchar2_aat;
l_integers integer_aat;
l_dates date_aat;
BEGIN
l_strings := random_strings (count_in, min_length_in, max_length_in);
l_integers :=
random_integers (count_in, min_integer_in, max_integer_in);
l_dates := random_dates (count_in, min_date_in, max_date_in);
DBMS_OUTPUT.put_line ('Random Strings:');
FOR indx IN l_strings.FIRST .. l_strings.LAST
LOOP
DBMS_OUTPUT.put_line (l_strings (indx));
END LOOP;
DBMS_OUTPUT.put_line ('Random Integers:');
FOR indx IN l_integers.FIRST .. l_integers.LAST
LOOP
DBMS_OUTPUT.put_line (l_integers (indx));
END LOOP;
DBMS_OUTPUT.put_line ('Random Dates:');
FOR indx IN l_dates.FIRST .. l_dates.LAST
LOOP
DBMS_OUTPUT.put_line (TO_CHAR (l_dates (indx), 'YYYYMMDDHH24MISS'));
END LOOP;
END random_verifier;
END randomizer; Package Body created.
Generate Random Strings
DECLARE
l_strings randomizer.maxvarchar2_aat;
BEGIN
l_strings :=
randomizer.random_strings (count_in => 100
, min_length_in => 3
, max_length_in => 20
, string_type_in => 'x'
, distinct_values_in => TRUE
);
FOR indx IN 1 .. l_strings.COUNT
LOOP
DBMS_OUTPUT.put_line (l_strings (indx));
END LOOP;
END; CXGBEWHO9MD4N
0QE4YK3JFUBSWYGURBN
6MV1I9D8C58ZG
D7ZGZ1GOQC
UR80W6K
IZI0RDAQBJW8P0VE
W70EXFHNAJ6
S3O8IG5QHQSJ
ODQ8OBOF0A3Z85926B6
FK1CDFIL7Z
VD453SBPKHNSCUHKNG
V5CLA1T8UVAJAEF931H
MNYZQ91M0UCERJK5X6D
Z3V0YXWWDCG7BFWLEE7A
6MLFVP8LT924DZKLVV
LHTBA
Z6YSGTD
LEZ
8KQ5YHPFRWBRP
V1M
FMSDO6
YUUG
X74B1
KAWL7L0O6TU20SNDR
QAIL0FLNMW8CK9
SNTRGBZ7
92QH88ROINW1
6YJIFYL
ILIWS8ZCZ70D938NDC5
5ETG9B58Q1AHT
5UZH
7ZOVW0AWJAO2K
FFRXN5IA9
S9578KTVC11G5OQ0J
8T2
P9QPAC9QF08
35K4SWBQJP15FBXWY
RZ3JYGJIB9VLV
MPTIX
QSI3M5KOL8W0QIJ7AN
VDOI6U6EUOC6OO
OE5N8KPGJ1R
S6RAK66F1K
XXBHQDAPKEV5RR036W4
BDLGY
VR7
NELPMEL
F1YXX6K6VHHBAQF6EW
79YMX464IKCNF55U2AW
HXGB6IMG1AKW
C7TJ82FHJ7L0MBDKNY
BCADSL2ZER1FWK
YD7AJU45HJR
R6L4DNUAPXASY03
E4NKETLWFR45V4Z4Y6
D1MB4U
9XEB5XYSPVCDL6XP98
UEO4G50MFZ8ZXHS4
TDJU17HKAWWGLTGQ
ZBEX0Q45XP7OH4FKG
AG9FPK1XRT35
7BBESVVZB81PPM0V
GQ12WFD08
Q4VWD8
LHC
FVPLZ5VFKXINTVZL58JI
XQRDK9
62LFQ21Q7XB0BI
ZM3G1LSJRE3RSFY868
VBZJD0TBLGQ
HS07TYCSEK6
850AWGE7B
2A0GF2IC
0JEBLRS
MQEU4H0UAG8JXRTQ
8FABALTXZHY
YRXMU9BTBWKGOY095ZIR
LW1DKKPVT3FSRXBHU
3PBYWDBLFLBPT3V1
BICQ2F83O
S6TRCVBGO8WI
ZVAIZO5R499
KAZ6
TQ372XBXSE
0DJB
SVPRKZ7
RTR3QJ957SFZGSWOOKZ
O2B2
RCUZE
LKUATHK3Z
HZTC8V
FOU82J0VK3X
YMQSNPTXD0NV1ZW
A7J
F631YCG5Y8JF
URT6SR
8JCHNQ9CQJF090R
Y7XLXCCKUFO8RNQIQV
3WC
PWW679BB52
Generate Random Integers
DECLARE
l_integers randomizer.integer_aat;
BEGIN
l_integers :=
randomizer.random_integers (count_in => 25
, min_value_in => 1
, max_value_in => 25
, distinct_values_in => TRUE
);
FOR indx IN 1 .. l_integers.COUNT
LOOP
DBMS_OUTPUT.put_line (l_integers (indx));
END LOOP;
END; 18
11
4
5
7
3
24
2
12
10
25
22
13
15
20
6
17
23
19
14
8
9
16
1
21