Every developer nowadays come up with problem of generating some random number or characters. There are some many in-build functions in popular programming languages. Out of which i am going to explain about ORACLE in build package DBMS_RANDOM.
The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. You can also specify the size and the range for random value.
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():
The VALUE() function generates random numbers from the range provided. The range will be taken as 0-1 if none is provided.
STRING(format,size):
This function is used to generate strings in upper case, lower case or alphanumeric format.
- The first parameter takes the string type to be generated, the following values can be provided in upper or lower case.
- U – Upper case
- L – Lower case
- A – Alphanumeric
- X – Alphanumeric with upper case alphabets.
- P – Printable characters only.
Providing any other character will return the output in upper case only.
The size of the string should also be provided as the second parameter.
Oracle documentation says that it is necessary to initialize the package before using the random number generator. Oracle by default initializes the package with the seed value as the current user name, current time down to the second and the current session id.
INITIALIZE();
This function is used to initialize package to proceed with the number generation.
Provide a number (seed) as input to the routine.
SEED():
This function is used to used to change the seed value. It is used in the internal algorithm to generate values. Setting this will generate the random numbers in an order that will be similar in multiple sessions. Refer to the example below.
TERMINATE – Close the process of random number generation.
Examples:
Below are some examples of using the package.
E.g.: Generating a random number (positive or negative)
SQL> select dbms_random.random from dual;
RANDOM
_____________
-1636133694
E.g.: Generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;
VALUE
_____________
0.183110555614124
E.g.: Generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;
NUM
__________________________
203.118060244321
SQL> select dbms_random.value(1000000000, 9999999999) num from dual;
NUM
_____________
5039410291.74104
E.g.: Generating an upper case string of 10 characters
SQL> select dbms_random.string('U', 10) RSTR from dual;
RSTR
_______________________
KXKBEDKHHT
E.g.: Generating a lower case string of 10 characters
SQL> select dbms_random.string('L', 10) RSTR from dual;
RSTR
____________________
gugfytusvb
SQL> select dbms_random.string('A', 10) RSTR from dual;
RSTR
__________________
OPOcDGGBJT
E.g.: Generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) RSTR from dual;
RSTR
________________________
4KWLJMH5GGA1APFF91UQ
SQL> select dbms_random.string('P', 20) RSTR from dual;
RSTR
___________________
H<“aSK!Q’AOQq4AkEeCE