How to use DBMS_RANDOM Package In SQL

The DBMS_RANDOM package provides random generation capabilities. It has several functions but we are going to make use of the RANDOM function from the package.

The RANDOM function generates integers in the range [-2^31, 2^^31).

Syntax:

DBMS_RANDOM.RANDOM

Example: The following query uses the RANDOM function

Query:

SELECT DBMS_RANDOM.RANDOM FROM DUAL;

Output:

Explanation: In this above query, DBMS_RANDOM.RANDOM is used to generate a random number between 0 and 1. FROM DUAL is a special construct in Oracle used to select a single row from a dummy table. The query returns a single random number.

We can use the DBMS_RANDOM.RANDOM function to select a random record from the table. We will make use of the function in the ORDER BY clause and then use row_number to filter the records. The following query implements the above logic.

Query:

SELECT * FROM (
    SELECT * FROM test
    ORDER BY DBMS_RANDOM.RANDOM
)
WHERE rownum<2;

Output:

Explanation: In the above query, we have selects a single random record from the test table. It does this by first ordering the records randomly using DBMS_RANDOM.RANDOM in the inner query and then limiting the result to one row using rownum<2 in the outer query.

In database management selecting random records from a table can be a useful operation for various applications such as sampling data, generating random samples for testing or selecting winners in a lottery.

In this article, we will explore different methods to select random records from a table in PL/SQL, Oracle’s procedural language extension for SQL. By the end of this article, you will understand how to use different approaches to achieve this task effectively.