Working with Random Numbers using DBMS_RANDOM package

DBMS_RANDOM package contains various functions and procedure by which we can get a help in order to generate random number in oracle.
1)NORMAL Function:
This function returns random numbers in a standard normal distribution.
Exmaple:
SQL> SELECT DBMS_RANDOM.NORMAL FROM DUAL;
NORMAL
----------
-.23955522
SQL> /

NORMAL
----------
-2.2293595


2)RANDOM Procedure:
This procedure generates a random number greater or equal to -power(2,31) and less than power(2,31).

Example:

SQL> SELECT DBMS_RANDOM.RANDOM FROM DUAL;
RANDOM
----------
-1.186E+09

3)STRING Function
This function gets a random string.
The syntax of this function is

DBMS_RANDOM.STRING
opt IN CHAR,
len IN NUMBER)
RETURN VARCHAR2;

opt Specifies what the returning string looks like:

'u', 'U' - returning string in uppercase alpha characters

'l', 'L' - returning string in lowercase alpha characters

'a', 'A' - returning string in mixed case alpha characters

'x', 'X' - returning string in uppercase alpha-numeric characters

'p', 'P' - returning string in any printable characters.

Otherwise the returning string is in uppercase alpha characters.

len specifies the length of the returning string.

Example:

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

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

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

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

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

DBMS_RANDOM.STRING('L',6)
----------------------------------------------
okyvoy
4)VALUE Functions
The basic VALUE function that is function with no argument gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision).

Example:

SQL> SELECT DBMS_RANDOM.VALUE FROM DUAL;

VALUE
----------
.25756407

VALUE function can also have syntax as

DBMS_RANDOM.VALUE(
low IN NUMBER,
high IN NUMBER)
RETURN NUMBER;

Where you can get a random Oracle number x, where x is greater than or equal to low and less than high.
Example:
To get value between 100 and 200 use,
SQL> SELECT DBMS_RANDOM.VALUE(100,200) FROM DUAL;

DBMS_RANDOM.VALUE(100,200)
--------------------------
126.700714

You can avoid the floating point part by using ROUND like,
SQL> SELECT ROUND(DBMS_RANDOM.VALUE(100,200),0) FROM DUAL;

ROUND(DBMS_RANDOM.VALUE(100,200),0)
-----------------------------------
101

Related Documents:
Use of Hint to Use Index