Ever wish you could just get some random numbers for test data? Tired of selecting counts from dictionary tables?

Oracle DOES have a couple of nice random number generators.

They are both in the DBMS_RANDOM package.

DBMS_RANDOM.Value( low, high)   is a function that will return random numbers to 7 decimal positions between the low range and the high range.

This one is my personal favorite.

Use TRUNC or ROUND to get to either whole numbers or 2 decimal positions!

This anonymous block shows DBMS_RANDOM.Value in action…generating numbers between 100 and 1000 …10 times.

This illustration is showing DBMS_RANDOM.Value returning whole numbers from a select line. You can use this function in the data loader, insert statements, wherever functions can be called!

A slightly different version of the DBMS_RANDOM.Value function is its friend ‘Normal’. DBMS_RANDOM.Normal returns numbers centered around 10,000 numbers on either side of 50,000. A bit more tricky syntax but it works the same and in the same places.

If you have been in any of my user group presentations, you will see me use DBMS_RANDOM.Value to do the book draw! I give away TOAD Handbooks and my Oracle SQL Tuning: A Close Look at Explain Plans and I let Oracle RDBMS select the winners!!!

You can even use DBMS_RANDOM.Value to code your own solitaire game!


Dan Hotka
Oracle ACE Director