Friday, June 12, 2009

Generating random numbers and strings in Oracle is easier

For testing purposes at times we may need some test data, for this we might need to generate some random numbers or strings. People may write their own Random number generation scripts, but even ORACLE is providing a package for generating the random numbers and strings as well. This may be useful to in creating the auto generated passwords in a web page.

The fact is that Oracle provides us with a random number generation package. When this package is used to generate the random numbers it is faster than writing our own random generation scripts in PL/SQL. This Random generator package is not only used to generate the numbers and strings but this can be used to generate the alpha numeric strings also with the desired size (number of characters).



The DBMS_RANDOM package which is provided by ORACLE will generate random data in character, numeric or alphanumeric formats. The size of the random values which are to be generated can also be specified.
This package can be created using the script dbmsrand.sql available in the /RDBMS/ADMIN directory.

The following are the functions that are present in the package which can be used to serve the purpose of generating random numbers and strings.
RANDOM – This function is used to generate the random numbers.

VALUE – This function is used to generate random numbers from the range provided. The range will be taken as 0-1 if nothing is provided.

STRING – This function is used to generate the strings either in upper case or lower case or alphanumeric format.

• The first argument of the functions takes the following values which will define the string type to be generated.
• U - Upper case
• L - Lower case
• A - Alphanumeric
• X - Alphanumeric with upper case alphabets.
• P - Printable characters only.

If we use any character other than these characters the output will be in Upper case.
The second argument will be the size of the random number / string to be generated.
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 - Initialize the package to proceed with the number generation.
Provide a number (seed) as input to the routine.

SEED - 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.


Below are some of the examples of using the package.

Example 1: To generate a random number (positive or negative)
Select dbms_random.random from dual;


Example 2: To generating a random number between 0 and 1.
SQL> select dbms_random.value from dual;


Example 3: To generating a random number from a range, between 1 to 1000.
SQL> select dbms_random.value(1,1000) num from dual;


Example 4: To generating a 12 digit random number.
SQL> select dbms_random.value(100000000000, 999999999999) num from dual;


Example 5: To generating an upper case string of 20 characters
SQL> select dbms_random.string('U', 20) str from dual;


Example 6: To generating a lower case string of 20 characters
SQL> select dbms_random.string('L', 20) str from dual;


Example 7: To generating an alphanumeric string of 20 characters. There is a bug in Oracle 8i that results in special (non-alphanumeric) characters such as ']' in the string. This is resolved in Oracle 9i.
SQL> select dbms_random.string('A', 20) str from dual;


Example 8: To generating an upper case alphanumeric string of 20 characters
SQL> select dbms_random.string('X', 20) str from dual;


Example 9: To generating a string of printable 20 characters. This will output a string of all characters that could possibly be printed.
SQL> select dbms_random.string('P', 20) str from dual;


Subscribe to Oracle Bullets by Email


  1. I will try to answer your question as a new post shortly.

  2. I recently came accross your blog and have been reading along. I thought I would leave my first comment. I dont know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.