Random number and string generator

by Granville Bonyata on July 15, 2012

There are a number of posts floating around the forums that dbms_random is deprecated in 11g. Apparently it all started here at psoug.org because someone there can’t read and understand the manual.

What the 11gR1 manual says is that three of the sub-programs have been deprecated: INITIALIZE, RANDOM and TERMINATE.

Now that this is cleared up, on to the primary uses of dbms_random:

Alphanumeric string

  • Mixed Case Alpha


    • SQL> select dbms_random.string('a',10) from dual;

    DBMS_RANDOM.STRING('A',10)
    --------------------------------------------------------------------------------
    MUPwYPLHdA

  • Alphanumeric

    SQL> select dbms_random.string('x',10) from dual;

    DBMS_RANDOM.STRING('X',10)
    --------------------------------------------------------------------------------
    32N0QLUYGT

  • Printable characters

    SQL> select dbms_random.string('p',10) from dual;

    DBMS_RANDOM.STRING('P',10)
    --------------------------------------------------------------------------------
    ElMMp49[pE

    Random numbers

  • Random real number between 0 and 1 (default behavior if no parameters)

    SQL> select dbms_random.value from dual;

    VALUE
    ----------
    .245249144

  • Random real number between a and b

    SQL> select dbms_random.value(1,10) from dual;

    DBMS_RANDOM.VALUE(1,10)
    -----------------------
    3.11236319

  • Random Integer
    There isn’t a specific call for this, so you have to truncate the decimals on a random number

    SQL> select trunc(dbms_random.value(1,10)) from dual;

    TRUNC(DBMS_RANDOM.VALUE(1,10))
    ------------------------------
    3

  • If you want a 5 digit number and don’t want to have to pad with leading zeros


    • SQL> select trunc(dbms_random.value(10000,99999)) from dual;

    TRUNC(DBMS_RANDOM.VALUE(10000,99999))
    -------------------------------------
    27008

    Getting predictable random numbers for testing purposes
    If you don’t explicitly seed a value (normally you should not), it uses a combination of the username, date and process id. If you’re testing and want the random generator to generate the same values for each test, you can seed it before each test with the same seed value. Here’s dbms_random.seed in action:

    SQL> exec dbms_random.seed(1000);

    PL/SQL procedure successfully completed.

    SQL> select dbms_random.value(1,1000) from dual;

    DBMS_RANDOM.VALUE(1,1000)
    -------------------------
    252.475371

    SQL> exec dbms_random.seed(1000);

    PL/SQL procedure successfully completed.

    SQL> select dbms_random.value(1,1000) from dual;

    DBMS_RANDOM.VALUE(1,1000)
    -------------------------
    252.475371

    If interested in the theory of generating random numbers, Wikipedia has a nice writeup.

    One last note – if you want to replace the dbms_random.random call and for some reason don’t want to use dbms_random.value, you can use dbms_crypto.randomnumber or dbms_crypto.randominteger.

    Previous post:

    Next post: