1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Generate Random Number

Discussion in 'Other Development Tools' started by Arju, Oct 10, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    It is too common to hear how to generate random number or string in oracle like we can do it in a simple calculator that has RAND function.

    Oracle supplied DBMS_RANDOM package does a great help in this. It comes with various procedure by which we can archive our goal.

    1)In order to get normal number distribution use,
    Code (Text):
    SQL>  SELECT DBMS_RANDOM.NORMAL FROM DUAL;

        NORMAL
    ----------
    -1.0835421

    Elapsed: 00:00:00.28

    SQL> run
      1*  SELECT DBMS_RANDOM.NORMAL FROM DUAL

        NORMAL
    ----------
    .348349028

    Elapsed: 00:00:00.00
    2)To get random number greater or equal to -power(2,31) and less than power(2,31) use,

    Code (Text):
    SQL> SELECT DBMS_RANDOM.RANDOM FROM DUAL;

        RANDOM
    ----------
     716619512
    3)To get random value between 0 and 1 use,
    Code (Text):
    SQL> SELECT DBMS_RANDOM.VALUE FROM DUAL;

         VALUE
    ----------
    .126327346
    To get value between 1 and 2 use,

    Code (Text):
    SQL> SELECT DBMS_RANDOM.VALUE(1,2) FROM DUAL;

    DBMS_RANDOM.VALUE(1,2)
    ----------------------
                1.64912316
    4)To get random string of 6 words long use, Here first argument of STRING function can be u,l,a,x or p. U indicates uppercase, l indicates lowercase, a indicate mixed case, x indicates uppercase alpha-numeric characters and p for any printable character.

    Code (Text):
    SQL> SELECT DBMS_RANDOM.STRING('',6) FROM DUAL;

    DBMS_RANDOM.STRING('',6)
    --------------------------------------------------------------------------------
    SHNIMR

    SQL> SELECT DBMS_RANDOM.STRING('a',6) FROM DUAL;

    DBMS_RANDOM.STRING('A',6)
    --------------------------------------------------------------------------------
    CWQeVY