DBMS_RANDOM Package - Oracle Random Number Generation

DBMS_RANDOM Package Overview

The DBMS_RANDOM package provides Oracle’s built-in random number and string generation capabilities. It’s essential for generating test data, creating unique identifiers, sampling data, and implementing randomization in Oracle applications.

Use at Your Own Risk

This documentation is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Package Overview

DBMS_RANDOM generates pseudorandom numbers using a linear congruential generator algorithm. The package is installed by default and available to all Oracle users.

Key Features

• Generate random numbers (integer and decimal)
• Create random strings with customizable character sets
• Produce random dates and timestamps
• Seed control for reproducible sequences
• Thread-safe operation in multi-user environments

Core Functions and Procedures

DBMS_RANDOM.VALUE

Generates random decimal numbers between 0 and 1, or within a specified range.

-- Basic syntax
DBMS_RANDOM.VALUE RETURN NUMBER
DBMS_RANDOM.VALUE(low NUMBER, high NUMBER) RETURN NUMBER

Examples:

-- Generate random number between 0 and 1
SELECT DBMS_RANDOM.VALUE FROM DUAL;
-- Result: 0.123456789

-- Generate random number between 1 and 100
SELECT DBMS_RANDOM.VALUE(1, 100) FROM DUAL;
-- Result: 45.7892341

-- Generate random integer between 1 and 10
SELECT FLOOR(DBMS_RANDOM.VALUE(1, 11)) AS random_int FROM DUAL;
-- Result: 7

DBMS_RANDOM.NORMAL

Generates normally distributed random numbers with mean 0 and standard deviation 1.

-- Generate normally distributed random number
SELECT DBMS_RANDOM.NORMAL FROM DUAL;
-- Result: -0.234567891

DBMS_RANDOM.STRING

Generates random strings using specified character sets.

DBMS_RANDOM.STRING(opt CHAR, len NUMBER) RETURN VARCHAR2

Character Set Options:

• 'U' - Uppercase letters (A-Z)
• 'L' - Lowercase letters (a-z)
• 'A' - Mixed case letters (A-Z, a-z)
• 'X' - Uppercase letters and numbers (A-Z, 0-9)
• 'P' - Printable characters (excluding spaces)

Examples:

-- Generate 8-character uppercase string
SELECT DBMS_RANDOM.STRING('U', 8) FROM DUAL;
-- Result: HKJLMNOP

-- Generate 10-character mixed case string
SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL;
-- Result: HkJlMnOpQr

-- Generate 6-character alphanumeric string
SELECT DBMS_RANDOM.STRING('X', 6) FROM DUAL;
-- Result: H3K7M9

DBMS_RANDOM.SEED

Sets the seed value for reproducible random sequences.

-- Seed with specific number
EXEC DBMS_RANDOM.SEED(12345);

-- Seed with string (converted to number)
EXEC DBMS_RANDOM.SEED('MYSEED');

-- Seed with current timestamp
EXEC DBMS_RANDOM.SEED(TO_CHAR(SYSDATE, 'DDMMYYYYHH24MISS'));

DBMS_RANDOM.INITIALIZE

Initializes the random number generator (deprecated in newer versions).

-- Initialize with seed value
EXEC DBMS_RANDOM.INITIALIZE(98765);

DBMS_RANDOM.TERMINATE

Terminates the random number generator session (deprecated).

EXEC DBMS_RANDOM.TERMINATE;

Practical Examples

Generate Random Test Data

-- Create sample employee data
SELECT
    DBMS_RANDOM.STRING('U', 3) || LPAD(FLOOR(DBMS_RANDOM.VALUE(1, 1000)), 3, '0') AS emp_id,
    DBMS_RANDOM.STRING('A', 8) AS first_name,
    DBMS_RANDOM.STRING('A', 10) AS last_name,
    FLOOR(DBMS_RANDOM.VALUE(25000, 100000)) AS salary,
    DATE '2020-01-01' + FLOOR(DBMS_RANDOM.VALUE(0, 1461)) AS hire_date
FROM DUAL
CONNECT BY LEVEL <= 10;

Random Sampling

-- Select 10% random sample from large table
SELECT *
FROM (
    SELECT *, DBMS_RANDOM.VALUE() AS rnd
    FROM large_table
    ORDER BY rnd
)
WHERE ROWNUM <= (SELECT COUNT(*) * 0.1 FROM large_table);

Generate Random Passwords

-- Generate secure 12-character password
SELECT
    DBMS_RANDOM.STRING('U', 3) ||
    DBMS_RANDOM.STRING('L', 3) ||
    FLOOR(DBMS_RANDOM.VALUE(100, 999)) ||
    DBMS_RANDOM.STRING('P', 3) AS password
FROM DUAL;

Random Date Generation

-- Generate random dates in 2023
SELECT
    DATE '2023-01-01' + FLOOR(DBMS_RANDOM.VALUE(0, 365)) AS random_date
FROM DUAL
CONNECT BY LEVEL <= 5;

Weighted Random Selection

-- Weighted random selection with bias
WITH weights AS (
    SELECT 'A' item, 50 weight FROM DUAL UNION ALL
    SELECT 'B' item, 30 weight FROM DUAL UNION ALL
    SELECT 'C' item, 20 weight FROM DUAL
),
cumulative AS (
    SELECT item, weight,
           SUM(weight) OVER (ORDER BY item) AS cum_weight,
           SUM(weight) OVER () AS total_weight
    FROM weights
)
SELECT item
FROM cumulative
WHERE DBMS_RANDOM.VALUE(0, total_weight) <= cum_weight
AND ROWNUM = 1;

Performance Considerations

Efficiency Tips

1. Minimize Function Calls: Store random values in variables when used multiple times
2. Batch Generation: Generate multiple random values in single query when possible
3. Seeding Strategy: Use appropriate seeding for your use case

-- Efficient: Generate once, use multiple times
DECLARE
    v_random NUMBER := DBMS_RANDOM.VALUE(1, 100);
BEGIN
    -- Use v_random multiple times
    INSERT INTO table1 VALUES (v_random, 'data1');
    INSERT INTO table2 VALUES (v_random, 'data2');
END;
/

Memory Usage

DBMS_RANDOM has minimal memory overhead and is designed for high-performance operations.

Common Use Cases

1. Test Data Generation

Generate realistic test datasets for development and testing environments.

2. A/B Testing

Randomly assign users to different test groups for statistical analysis.

3. Load Balancing

Distribute workload randomly across multiple resources or servers.

4. Gaming Applications

Implement random events, dice rolls, and probability-based mechanics.

5. Security Applications

Generate random tokens, session IDs, and one-time passwords.

6. Statistical Sampling

Create random samples from large datasets for analysis.

Best Practices

Seeding Strategy

-- Production: Use time-based seed for uniqueness
EXEC DBMS_RANDOM.SEED(EXTRACT(SECOND FROM SYSTIMESTAMP) * 1000000);

-- Testing: Use fixed seed for reproducible results
EXEC DBMS_RANDOM.SEED(12345);

Error Handling

DECLARE
    v_random NUMBER;
BEGIN
    BEGIN
        v_random := DBMS_RANDOM.VALUE(1, 100);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Random generation failed: ' || SQLERRM);
            v_random := 50; -- Default value
    END;
END;
/

Thread Safety

DBMS_RANDOM is thread-safe and can be used safely in concurrent environments without additional synchronization.

Limitations and Considerations

1. Pseudorandom: Not cryptographically secure; use DBMS_CRYPTO for security purposes
2. Range Limitations: Maximum range depends on Oracle NUMBER precision
3. Performance: While fast, excessive calls in tight loops may impact performance
4. Seed Persistence: Seeds don’t persist across database restarts

Alternative Approaches

DBMS_CRYPTO.RANDOMBYTES

For cryptographically secure random values:

SELECT DBMS_CRYPTO.RANDOMBYTES(8) FROM DUAL;

SYS_GUID()

For unique identifiers:

SELECT SYS_GUID() FROM DUAL;

SAMPLE Function

For random sampling:

SELECT * FROM large_table SAMPLE(10);

Troubleshooting

Common Issues

Issue: Predictable sequences in concurrent sessions Solution: Use unique seeds per session

EXEC DBMS_RANDOM.SEED(USERENV('SESSIONID') + DBMS_UTILITY.GET_TIME);

Issue: Same random values across database restarts Solution: Initialize with timestamp-based seed at startup

Issue: Performance degradation with high-frequency calls Solution: Batch generate and cache random values when possible

Version Compatibility

• Oracle 8i+: Core DBMS_RANDOM functionality
• Oracle 9i+: Enhanced string generation options
• Oracle 11g+: Improved performance and thread safety
• Oracle 12c+: Additional character set support

Related Functions

• DBMS_CRYPTO - Cryptographic functions
• SYS_GUID - Unique identifier generation
• SAMPLE - Table sampling

Summary

DBMS_RANDOM is Oracle’s primary tool for generating pseudorandom values. It provides efficient, flexible random number and string generation suitable for most application needs. While not cryptographically secure, it offers excellent performance for test data generation, sampling, and general randomization requirements.

For production applications requiring cryptographic security, consider using DBMS_CRYPTO instead. Always test random generation patterns in your specific environment to ensure they meet your application’s requirements.