Generating Random Numbers Between 1 and 100 in Oracle SQL2 min read

In the world of database management, generating random numbers within a specific range is a common requirement. Oracle SQL provides robust functionalities to accomplish this task efficiently. In this article, we’ll explore how to generate random numbers between 1 and 100 in Oracle SQL, discussing different methods and use cases.

Using the DBMS_RANDOM Package:

Oracle SQL offers the DBMS_RANDOM package, which provides a variety of randomization functions. To generate a random number between 1 and 100, you can utilize the following code:

This query uses the DBMS_RANDOM.VALUE function to generate a random decimal number between 1 (inclusive) and 101 (exclusive). The FLOOR function is then applied to ensure the result is an integer.

Generating Multiple Random Numbers:

If you need a set of random numbers, you can extend this approach to generate a result set with a specified number of rows. For example, to generate ten random numbers between 1 and 100:

Here, the CONNECT BY clause is used to repeat the query for the desired number of rows.

Reproducible Random Numbers:

If you require reproducibility, you can use the SEED value with the DBMS_RANDOM package. The SEED ensures that the random number sequence is the same each time the query is executed. For instance:

Here, 42 is the seed value, and changing it will alter the generated sequence.

Applications and Use Cases:

Generating random numbers between 1 and 100 in Oracle SQL can be beneficial for various scenarios, including:

  1. Random Sampling: Selecting a random subset of data for analysis or testing.
  2. Data Masking: Masking sensitive information with realistic but random values.
  3. Simulations: Simulating random events or inputs in a controlled environment.


Oracle SQL provides powerful tools, particularly through the DBMS_RANDOM package, to generate random numbers within specified ranges. Whether you’re working on data analysis, simulations, or other applications, understanding how to generate random numbers between 1 and 100 can add versatility to your SQL toolkit. Experiment with the examples provided to integrate these techniques into your specific use cases.

Leave a Comment