Oracle PL/SQL for DBAs

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. ...