Home

Introduction

When testing or developing database applications, it’s often necessary to populate tables with dummy data. Oracle provides tools like DBMS_RANDOM to generate random data for testing. In this guide, we’ll demonstrate how to insert random strings, numbers, and dates into an Oracle table using PL/SQL.

Creating the Table

Start by creating a sample table for testing:

CREATE TABLE T1 (
    ID NUMBER,
    NAME VARCHAR2(20 BYTE),
    CREATED_DATE DATE
) TABLESPACE USERS;

Inserting Random Data

The following PL/SQL block inserts 1500 rows into the table. Each row includes a unique ID and a random string of 20 characters:

DECLARE
    i NUMBER := 1;
BEGIN
    LOOP
        i := i + 1;
        INSERT INTO T1 VALUES(i, (SELECT DBMS_RANDOM.STRING('A', 20) FROM DUAL));
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(i);
        EXIT WHEN i >= 1500;
    END LOOP;
END;

After running this block, you can confirm the number of rows inserted:

SELECT COUNT(*) FROM T1; -- Result: 1500 rows

Customizations

• Increase Data Volume: Change i >= 1500 to a higher value to insert more rows.
• Control Sequence: Modify i := i + 1 to i := i + 2 or any increment to adjust the ID sequence.

Using DBMS_RANDOM.STRING

The DBMS_RANDOM.STRING function generates random strings based on specified parameters:

• ‘U’: Uppercase alphabetic characters
• ‘L’: Lowercase alphabetic characters
• ‘A’: Mixed case alphabetic characters
• ‘X’: Uppercase alphanumeric characters
• ‘P’: Any printable characters

Adding Random Dates

To insert random dates, add a column to the table and use the following PL/SQL block:

DECLARE
    i NUMBER := 10;
BEGIN
    LOOP
        i := i + 10;
        INSERT INTO T1 VALUES(
            i, 
            (SELECT DBMS_RANDOM.STRING('A', 10) FROM DUAL), 
            (SELECT TRUNC(SYSDATE + DBMS_RANDOM.VALUE(0, 366)) FROM DUAL)
        );
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(i);
        EXIT WHEN i >= 150;
    END LOOP;
END;

This block generates random dates within the next year. Adjust the range in DBMS_RANDOM.VALUE(0, 366) to modify the date range.

Adding Random Timestamps

To include random hours, minutes, or seconds, use the following formula:

(TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.VALUE(0, 1000)) / 24))

This adds random hours to the current date.

Best Practices

• Use for Testing Only: Random data is ideal for testing but should not be used in production environments.
• Control Commit Frequency: For large datasets, control commits to avoid undo tablespace issues.
• Customize Data: Modify the INSERT statement to match your table structure and testing needs.

Conclusion

Generating random data using Oracle’s DBMS_RANDOM package simplifies testing and development tasks. By following the examples provided, you can populate your tables with realistic test data, including strings, numbers, and dates, tailored to your requirements.