TimArcher.com A blog about technology...and more.

Have you ever needed to generate a random number within your Oracle PL/SQL code? I recently had a need to just this. My requirement was to generate a random 6 digit pin number for users to login to our web systems with.

I found that the dbms_random package does exactly what I need, and is extremely simple to use.

My first test of this was just to run the statement:

select dbms_random.value
from dual

This will return a random number, greater than or equal to 0 and less than 1, with decimal 38 digits. When I ran this command my result was:
0.622324263489056

The DBMS_RANDOM.VALUE function can also take 2 arguments, the low number and the high number. By using these two arguments you can get a random Oracle number x, where x is greater than or equal to the low number and less than the high number.

For example to produce a random number between 1 and 1000, I ran the command:

select dbms_random.value (0, 1001)
from dual

Now for me, this returned a value with a bunch of decimal places. The value I got was:
195.957387577905

If you dont want the decimal places, you can use the Oracle TRUNC function to truncate off the decimals:

select trunc(dbms_random.value (0, 1001))
from dual

Great, my result now doesn’t have a decimal portion and looked like:
706

So, now to put it all together and get myself the random 6 digit number, I had to increase the value of my high number, and LPAD the result so that leading zeroes would always be included.

select lpad(trunc(dbms_random.value (0, 1000000)), 6, '0')
from dual

This worked like a champ, and was extremely simple to integrate into my code.