Section 7.2. Generating Characters | Oracle PL(s)SQL For DBAs

7.2. Generating Characters

The previous sections introduced randomizers, random functions, and seeding, but all of the examples included in those sections were oriented to random number generation. In this section, we'll look at the generation of random character strings using the DBMS_RANDOM package's STRING function . STRING accepts two parameters , opt and len.

The first parameter, opt, specifies the type of string to be generated. The following table lists possible values.

opt value	Effect
u	Generates uppercase alphabets only (e.g., DFTHNDSW)
l	Generates lowercase alphabets only (e.g., pikdcdsd)
a	Generates mixed-case alphabets (e.g., DeCWCass)
x	Generates a mixture of uppercase alphabets and numbers (e.g., A1W56RTY)
p	Generates any printable characters (e.g., $\$2sw&*)

The second parameter, len, specifies the length of the character string to be generated. This is necessary if your application requires a random string of a particular length. Using this function, I can build my first random string generator function as follows:

     CREATE OR REPLACE FUNCTION get_random_string (        p_len    IN   NUMBER,        p_type   IN   VARCHAR2 := 'a'     )        RETURN VARCHAR2     AS        l_retval   VARCHAR2 (200);     BEGIN        l_retval := DBMS_RANDOM.STRING (p_type, p_len);        RETURN l_retval;     END;     /

To produce a 40-character string of mixed-case letters using this newly created function, I can specify:

     SQL> EXEC DBMS_OUTPUT.put_line(get_random_string(40))     XaCbNwzpkGEsgqzCCdEykCycEtLlvoMOxrPnwanj     PL/SQL procedure successfully completed.     SQL> EXEC DBMS_OUTPUT.put_line(get_random_string(40))     hUctRtmsTWsedxqcTNNIlMDhyTgcQmmkyhrCwkUY     PL/SQL procedure successfully completed.

As shown in the table, I can use different values of the p_opt parameter to generate different types of random strings, such as only uppercase letters, only lowercase letters, a mixture of letters and numbers, and any printable characters.

As with numbers, I can specify a seed value for the generation of random strings, using the same SEED procedure, which is overloaded to accept a VARCHAR2 value. I can use the same technique of using the system time as the seed, but in this case I make it a character.

     BEGIN        DBMS_RANDOM.seed (TO_CHAR (SYSDATE, 'mmddhhmiss'));     END;

Let's revisit the original goal stated at the outset of this chapter: we wanted to develop test data with bank account names and balances, using good randomization techniques so that the account names and balances will be generated as truly random characters and numbers. We have already seen the technique for generating the account balance, but what about the name?

I could use my get_random_string function, but it currently always returns a string of the specified length. In real life, names are of varying lengths. To generate a sufficiently varied set of names, let's enhance our function to specify the minimum and maximum lengths and to randomize the string length within that range. Here is version two of our string randomizer:

     CREATE OR REPLACE FUNCTION get_random_string (        p_minlen   IN   NUMBER,        p_maxlen   IN   NUMBER,        p_type     IN   VARCHAR2 := 'a'     )        RETURN VARCHAR2     AS        l_retval   VARCHAR2 (200);     BEGIN        l_retval :=            DBMS_RANDOM.STRING (p_type                              , DBMS_RANDOM.VALUE (p_minlen, p_maxlen));        RETURN l_retval;     END;

In the DBMS_RANDOM.STRING call, I passed the length of the string parameter a random number between the parameter values passed. Let's test this function:

     SQL> EXEC DBMS_OUTPUT.put_line(get_random_string(10,20))     DPZmDqQFcwhnqRL     PL/SQL procedure successfully completed.     SQL> EXEC DBMS_OUTPUT.put_line(get_random_string(10,20))     goJVifYdSeQ     PL/SQL procedure successfully completed.

Note how the lengths of the strings generated in these two cases are different. Using this approach, I can generate random strings for a wide variety of applications, including account names, web site user IDs, and temporary passwords. The most valuable use in the context of this chapter, however, is the generation of seeds and keys for encryption, as we will see later on.

Let's build a program to populate account records with random data. The ACCOUNTS table looks like this:

     SQL> DESC accounts      Name                 Null?    Type      -------------------- -------- -------      ACCOUNT_NO                    NUMBER      BALANCE                       NUMBER(9,4)      ACCOUNT_NAME                  VARCHAR2(20)

The program to insert random values into the accounts table is:

     DECLARE        i   NUMBER;     BEGIN        FOR i IN 1 .. 10        LOOP           DBMS_RANDOM.seed (TO_NUMBER (TO_CHAR (SYSDATE, 'hhmiss')));           INSERT INTO accounts (account_no, balance, account_name)                VALUES (i                      , DBMS_RANDOM.VALUE (10000, 99999)                      , DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)));        END LOOP;     END;     /

After the PL/SQL block has been run, I can see the records inserted.

     SQL> SELECT * FROM accounts;     ACCOUNT_NO    BALANCE ACCOUNT_NAME     ---------- ---------- --------------------              1 91772.2043 FZIGIONR80U              2 91772.2043 FZIGIONR80U              3 91772.2043 FZIGIONR80U              4 91772.2043 FZIGIONR80U              5 91772.2043 FZIGIONR80U              6 91772.2043 FZIGIONR80U              7 91772.2043 FZIGIONR80U              8 91772.2043 FZIGIONR80U              9 86258.8032 65YJYV9NMMBGMRP5CMP7             10 86258.8032 65YJYV9NMMBGMRP5CMP7

Do you see that something is seriously wrong here? Pretty much all the balances are the same, and the names are the same, too. This does not look like a true randomized data set. What's the problem?

The problem is with the seeding value. Note that in line 6 of the code segment, I set the seed value to a combination of hours, minutes, and seconds. However, this program runs for less than a second, and the seed value is reset to the same value every time; hence, the random numbers generated are the same. In the above example, in iterations 1 through 8, the seed value was the same; hence, all the "random" values are simply the same. The seed changed at the ninth iteration, and that caused the "random" values to change.

To resolve this problem, I can just remove the seeding line from the above PL/SQL code. Here is the modified code with the line commented.

     DECLARE        i   NUMBER;     BEGIN        FOR i IN 1 .. 10        LOOP           -- DBMS_RANDOM.seed (TO_NUMBER (TO_CHAR (SYSDATE, 'hhmiss')));           INSERT INTO accounts                VALUES (i, DBMS_RANDOM.VALUE (10000, 99999),                        DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)));        END LOOP;     END;     /

Now if I check the values generated, I will see a truly randomized data set.

     SQL> SELECT * FROM accounts;     ACCOUNT_NO    BALANCE ACCOUNT_NAME     ---------- ---------- --------------------              1 18344.0416 ELR8PWCSIAPKF1POH              2  94702.904 XQGBFVQGQI8QPAV6NIN              3 64261.8317 POF99DU2DAHOXE5AC7              4 95369.3182 42IT6V7XOAF7              5 65451.8237 6ZU5H91XEGMV0              6 68695.4939 4VNP4KWJN6Y              7 71474.0692 90LNOSJNKE5CO              8 78402.0396 IG9Z3KEFZ35YCXIER9N              9 63726.3395 86JK18HJEON             10 12416.5512 JRQC39C5K0LA

Carefully review the lesson learned here. Resetting the seed to the same value results in a predictable value in the randomizer. Leaving the seed unchanged results in the generation of true random numbers. In the above code sample, the program ran too fast for seed changes, however. If we are not sure about the speed of the program and want to use the seed, how can we achieve randomness?

The solution is to throw another element into the seed, an element that will definitely change. In the code sample, the element could be the iteration variable i, which definitely changes inside the loop. So the code could be written as:

       1  DECLARE       2     i   NUMBER;       3  BEGIN       4     FOR i IN 1 .. 10       5     LOOP       6        DBMS_RANDOM.seed (i || TO_NUMBER (TO_CHAR (SYSDATE, 'hhmiss')));       7       8        INSERT INTO accounts       9             VALUES (i, DBMS_RANDOM.VALUE (10000, 99999),      10                     DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (10, 20)));      11     END LOOP;      12* END;

Note that in line 6, the seed value now has the value of i as a component, too. This guarantees a different seed value each time, even if the program runs too fast. Using this technique, we can code for all possible scenarios involving random numbers.

Never reset the seed to the same value it was before you called a randomizer function. If you use as a seed some variable whose value could potentially be the samesuch as the current timeuse another variable that is guaranteed to change, such as a loop counter variable.