SteveMcKeogh.co.uk - Web Developer

Those familiar with MySQL may have used the rand() function before to order a resultset randomly. Well there is a just as simple method in Oracle, just with different syntax.

To order your results in Oracle in a random order you use the dbms package (packaged with Oracle) called random. So a simple order random query would look like:

SELECT * FROM tableName
ORDER BY dbms_random.value

That's it, your results will now be ordered randomly every time the query is run. But there's more to dbms_random than that though, you can also use it to generate random numbers or strings. Take a look at some of the examples below:

Generate a random negative or positive number:
SELECT dbms_random.random FROM dual

Generate a random number between 1 and 9999:
SELECT dbms_random.value(1,9999) AS rand FROM dual

Generate a random UPPERCASE string of 10 chars:
SELECT dbms_random.string('U',10) AS rand FROM dual

Generate a random LOWERCASE string of 10 chars:
SELECT dbms_random.string('L',10) AS rand FROM dual

The dbms_random package can do a lot more than this, but they are a few examples that I use every now and then.