Oracle PL/SQL for DBAs

Generating Random Strings of Random Length

In DMBS_RANDOM.STRING, the string generated is random but of fixed length. In reality, however, people have last names of varying lengths. In this next example, the requirement is to have a length between 4 and 30 characters. To accommodate this requirement, I can pass the length as a random number as well to the STRING function in line 6 below.

      1  BEGIN
      2     FOR i IN 1 .. 10
      3     LOOP
      4        DBMS_OUTPUT.put_line (   'Random String='
      5                  || DBMS_RANDOM.STRING ('A'
      6                                        , DBMS_RANDOM.VALUE (4, 30)
      7                                                    )
      8                             );
      9     END LOOP;
     10* END;

The output is:

    Random String=RniQZGquFVJYFpGLOvtNd
    Random String=GhcphpcsaCXlhigRQY
    Random String=JtakoelUf
    Random String=BgCOu
    Random String=QFBzQxcHqGlHWkZFmnN
    Random String=lSxVjqJvpwBB
    Random String=jfhNARzALrLOKZRpOwnhrzz
    Random String=KuFtdJcqQpjkrFmzFbzcXnYFGjWo
    Random String=BhuZ
    Random String=GebcqcgvzBfEpTYnJPmYAQdb

Note how the generated strings are not only random but also of randomly different lengths.

I also need 25% of the last names to be “Smith” and the rest to have random values. I can accomplish that by combining the random strings and the Monte Carlo simulation as follows:

       DECODE (
             FLOOR(DBMS_RANDOM.value(1,5)),
                1,'Smith',
                DBMS_RANDOM.string ('A',DBMS_RANDOM.value(4,30))
          )

This expression will return “Smith” 25% of the time and a random alphabetic string between 4 and 30 characters the rest of the time.