How to Generate Random Number in Oracle

How to generate Random numbers in Oracle?

Oracle database provides DBMS_RANDOM build-in package which you can use to generate random numbers/characters.

This post is not about this package, I just want to share the practical experience I had using the DBMS_RANDOM package.

So Let me share my experience.

I was working on an assessment project in Oracle APEX. It was a simple question/answer type of project. The requirement was to display a set of questions to different users. Each time or for each different user the questions should get reshuffled.

You get what I said, right?

Now question is, How to achieve that random reshuffling?

Here, ORDER BY was an obvious choice, but what should be ordered by column name. It should be some random number that should change each time. Did some google and found an interesting package DBMS_RANDOM in Oracle.

This is the way it can be used with the ORDER BY Clause. The actual implementation was a little different. I will try to write a small article on it.

Below is a simple query based on dba_objects with the order by dbms_random.random.

SELECT object_id,
 owner,
 object_name,
 object_type
FROM dba_objects
ORDER BY dbms_random.random

Check the below result, ordering is different for each run.

Result 1

DBMS RAndom 1

Result 2

dbms random 2

You can clearly see the sort is different for each run. Even when you run the query in same session, the sorting remains distinct because dbms_random.random function generates random numbers each time.

This helped a lot and I could easily manage to finish that Oracle Apex Project.

Summary

I hope you found this small tip useful.

I will also try to create an article on DBMS_RANDOM Package which will show how to use this package and the number of functionalities provided by the package.

Till that time, keep sharing this article, and do let me know if there is any other way to achieve it?