Thursday, February 18, 2016

Oracle Database Random Function in PLSQL

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified.

The following functions present in the package can be used to serve the purpose of generating random numbers and strings. RANDOM - generate random numbers.
VALUE - generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING - generate strings in upper case, lower case or alphanumeric format.

Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;

       RANDOM
_____________
   1393936551
 
Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;

        VALUE
_____________
            1

Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;

          NUM
_____________
          611

Generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;

          NUM
_____________
 175055628780

Generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;

STR
_______________________
VUOQOSTLHCKIPIADIZTD
 
 
Generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;

STR
____________________
xpoovuspmehvcptdtzcz

Generating an alphanumeric string of 20 characters.
SQL> select dbms_random.string('A', 20) str from dual;

STR
__________________
sTjERojjL^OlTaIc]PLB
 
 
Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;

STR
________________________
SQ3E3B3NRBIP:GOGAKSC

Generating a string of printable 20 characters.
SQL> select dbms_random.string('P', 20) str from dual;

No comments: