DBMS_RANDOM.RANDOM – Generate random number

DBMS_RANDOM is build-in package in Oracle Database to generate random number/characters.

Why I need such package or should use it?

Let me share my experience, I was working on 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.

How to achieve it?  ORDER BY was an obvious choice, but what should be order by column name. It should be some random number which should change each time. Did some google and found interesting package DBMS_RANDOM in Oracle.

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

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

 

Check below result, ordering is different for each run.

Result 1

dbms_random-1

 

Result 2

dbms_random-2

Any other way to achieve it?

Related Post