Quseful #6: Random Values

Home

Products

Community

Experts

Knowledge

Downloads

Hello, you are not logged in. Login or sign up

Experts >> Steven Feuerstein's PL/SQL Obsession >> Quick and Useful Tips (Qusefuls) >> Quseful #6: Random Values


		Quseful #6: Generate Collections of Random Values

You will find in this Quseful a package that will generate/return collections of random values of strings, numbers and dates. It also contains a "self-test" random_verifier procedure that you can run to verify "at a glance" that the values being generated seem random. As a bonus, I include the pick_winners_randomly procedure, which I use in my seminars to pick the winners in raffles for my books and other goodies.

I wrote this package in August 2007 so that I could implement automatic boundary condition test generators for Quest Code Tester for Oracle. For example, if I have a function that accepts a string and a number and returns a date, then I would like to verify that if I pass in NULL for the string, then no matter what value I pass in for the number, my function always returns NULL. To do this, I need to generate a random set of values for the number.

Show me the code!

I certainly won't show you all the code in this entry. Download full source code from this zip file.

I can very quickly show you, however, that to generate random values in PL/SQL, you will take advantage of the DBMS_RANDOM package. You will find a very thorough explanation of this package and how to use it in my (and Arup Nanda's) book Oracle PL/SQL for DBAs. Do check it out and buy a copy or two!

Briefly (and to simplify matters a bit), you can call one of these two programs:

DBMS_RANDOM.VALUE – return a random integer within the specified range

DBMS_RANDOM.STRING – return a random string of the specified type and length.

You can then also combine them in various ways. For example, if I want to generate random strings with random lengths, I can do something like this:

FUNCTION random_string (

min_length_in IN PLS_INTEGER DEFAULT 1

, max_length_in IN PLS_INTEGER DEFAULT 100

, string_type_in IN VARCHAR2 DEFAULT NULL

)

RETURN maxvarchar2_aat

BEGIN

RETURN DBMS_RANDOM.STRING (

string_type_in

, DBMS_RANDOM.VALUE (min_length_in, max_length_in)

);

END random_string;

But that returns just a single string. In this package, you will find programs that return a set of random values of the specified types as an associative array.

Note 1: You may want to convert this package to return nested tables so that you can call the random value generators inside a SELECT statement.

Note 2: Check out the way that I use a string-indexed collection to easily make sure that random values are unique.

How do I use it?

To make the functions as useful as possible, I provide arguments that allow you to specify the number of random values you need, whether or not you require distinct values (no repeats), and the range over which the values can vary.

When generating strings, you can also specify the type of string you desire. Oracle offers these options:

u - uppercase

l - lowercase

a - mixed case

x - mix of uppercase and digits

p - any printable character

So in the following block of code, I obtain a list of 500 random strings with a minimum of 6 characters and a maximum of 20 characters, using a mix of uppercase and digits:

DECLARE

l_strings randomizer.maxvarchar2_aat;

BEGIN

l_strings :=

randomizer.random_strings (count_in => 500

, 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;

Try it out – I think you will agree these are pretty random-looking strings!