Unwrapping Oracle's DBMS Packages: Understanding Oracle's Random Number Generator - Page 2

The script to create the DBMS_RANDOM package is dbmsrand.sql, and is located in the ORACLE_HOME\rdbms\admin directory. You can see (at the end of it) how the range of numbers returned is between -power(2,31) and power(2,31). Note that the output can be negative as well as positive. For a million random numbers, and using the RANDOM argument, and assuming the random numbers are truly random, you could expect the range to cover quite a bit of the interval in [-2147483648, 2147483648] and to have an average of zero and a sum of zero. However, given the magnitude of the upper and lower bounds, one large value in either direction can "swamp" the results. In the million row table, there are 499817 numbers less than zero, so it would not be surprising to see both the sum and average having values above zero.

The minimum and maximum values returned in an earlier query were -2147479960 and 2147480366. This range missed the lower end by 3688 and the upper end by 3282, or put another way, the million row table covered over 99.999% of the possible range of values.

This is the description of the STRING function found in the DBMS_RANDOM package:

    -- get a random string
    FUNCTION string (opt char, len NUMBER)
          /* "opt" specifies that the returned string may contain:
             'u','U'  :  upper case alpha characters only
             'l','L'  :  lower case alpha characters only
             'a','A'  :  alpha characters only (mixed case)
             'x','X'  :  any alpha-numeric characters (upper)
             'p','P'  :  any printable characters
          */
        RETURN VARCHAR2;  -- string of  characters (max 60)

Here is an example using the "P" option:

SQL> DECLARE
  2    v_str varchar2(100);
  3  BEGIN
  4    DBMS_RANDOM.INITIALIZE (123456);
  5    FOR i IN 1..10 LOOP
  6     v_str := DBMS_RANDOM.string('p',20);
  7     dbms_output.put_line(i||': '||v_str);
  8    END LOOP;
  9  END;
 10  /

1: wCqsq!`+\PVNXn!uEip,
2: kx5di5yaEC2 =~XQ! NI
3: $Am`fz^wH!VQevIaXlU7
4: Dr,yO0 YoP?I+_mRss]2
5: 6Q3+:[buk/hEs[CTQn;V
6: K~BSaD$Zk(to>iB^Oop<
7: ?i c,c}]O))@r!fxv8f'
8: cWe+x,%DK5pqX<;Xb@21
9: N.{_)[h6")f3HWG8u&)X
10: xnP)FDyVBx*EGbfl3OA{

PL/SQL procedure successfully completed.

No doubt about it, the "P" option returns quite the array of gibberish. The use of only alpha characters in the STRING function can help generate alphabet replacement type of cryptograms. It can also be used to generate passwords. You may want to combine two functions to produce varying case and numbers, and a simple example is shown below:

SQL> DECLARE
  2    v_str varchar2(100);
  3  BEGIN
  4    DBMS_RANDOM.INITIALIZE (123456);
  5    FOR i IN 1..10 LOOP
  6     v_str := DBMS_RANDOM.string('X',6);
  7     dbms_output.put_line(i||': '||v_str);
  8    END LOOP;
  9  END;
 10  /

1: XDUVU0
2: O4MIKH
3: LT0WER
4: U4SX8Q
5: R8YOED
6: 60AZLI
7: 00HF1C
8: TOQYNX
9: F0KIQW
10: FOLSK8

The other function hidden in the package returns normally distributed (bell curve) random numbers. Distributions that are not normally distributed tend to become normally distributed when you start collecting a lot of them. The standard Normal distribution has a mean of zero and a variance of one, and those results should be expected when looking at a large sample of random numbers. The million row table created using DBMS_RANDOM.NORMAL shows the following results (table name of NORM, columns named LINE and RNORM):

SQL> select avg(rnorm), variance(rnorm)
  2  from norm;


AVG(RNORM) VARIANCE(RNORM)
---------- ---------------
-.00012847      1.00006502

The minimum and maximum values of -4.9973893 and 4.85893083 (see below) correspond to observations in the far end of each tail, or in other words, extreme values of area (close to zero and close to 100% of the area under the curve).

SQL> select min(rnorm), max(rnorm)
  2  from norm;

MIN(RNORM) MAX(RNORM)
---------- ----------
-4.9973893 4.85893083

In closing, this exploration or unwrapping of the DBMS_RANDOM package should have surfaced some new (but old, really) features of Oracle for you and given you some insight into the nature of the numbers produced by this package. Random numbers are used in many places in science and engineering, and as we will see in a later article, in Oracle databases.

» See All Articles by Columnist Steve Callan