DBMS_RANDOM generates random numbers and strings in PL/SQL and SQL. It is the quick way to build test data, pick random rows, or shuffle results. Setting a seed makes the sequence repeatable, so tests produce the same data every run.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_random.seed(number_or_string); dbms_random.value -- 0 <= n < 1 dbms_random.value(low, high) -- low <= n < high dbms_random.normal -- normal distribution, mean 0 dbms_random.string(option, length)
The STRING options are U for uppercase, L for lowercase, A for mixed case, X for uppercase and digits, and P for any printable character.
Numbers and Strings
Example:
begin
dbms_random.seed(2026); -- repeatable sequence
dbms_output.put_line('value: ' || round(dbms_random.value, 6));
dbms_output.put_line('1 to 100: ' || trunc(dbms_random.value(1, 101)));
dbms_output.put_line('normal: ' || round(dbms_random.normal, 4));
dbms_output.put_line('upper: ' || dbms_random.string('U', 6));
dbms_output.put_line('mixed: ' || dbms_random.string('X', 8));
dbms_output.put_line('any: ' || dbms_random.string('p', 10));
end;
/
select airport_code from airports order by dbms_random.value fetch first 3 rows only;Output:
value: .209462 1 to 100: 8 normal: -1.0157 upper: UAFRPF mixed: JTWJ0QER any: j''V>X@lW( PL/SQL procedure successfully completed. AIRPORT_CODE _______________ BLR AMS NBO
TRUNC(VALUE(1, 101)) gives a whole number from 1 to 100, since VALUE never reaches its upper limit. ORDER BY DBMS_RANDOM.VALUE shuffles rows, and FETCH FIRST picks three random airports. With seed 2026, the PL/SQL values are the same on every run.
Generate Test Rows
Example:
exec dbms_random.seed('test data')
select level as id,
initcap(dbms_random.string('L', 6)) as passenger,
round(dbms_random.value(150, 900), 2) as fare,
date '2026-11-01' + trunc(dbms_random.value(0, 30)) as travel_date
from dual
connect by level <= 5;Output:
PL/SQL procedure successfully completed.
ID PASSENGER FARE TRAVEL_DATE
_____ ____________ _________ ______________
1 Ikmwct 171.04 15-NOV-2026
2 Xytuec 661.04 10-NOV-2026
3 Whcwkt 693.51 11-NOV-2026
4 Glphoy 194.71 16-NOV-2026
5 Jucble 526.64 19-NOV-2026CONNECT BY LEVEL produces five rows, each with a random name, fare, and November date. The seed can be a string too.
Things to Know
- DBMS_RANDOM is not suitable for passwords, keys, or tokens; use DBMS_CRYPTO.RANDOMBYTES for those.
- Without a seed, each session starts from a different random sequence.
- For a random sample of a large table, the SAMPLE clause is faster than ORDER BY DBMS_RANDOM.VALUE, which sorts every row.
Related Guides
Conclusion
DBMS_RANDOM returns random numbers in a range, normally distributed values, and random strings, repeatably when seeded. Use it for test data and shuffling, the SAMPLE clause for sampling large tables, and DBMS_CRYPTO for anything security related.
See my articles more often when you search Google.
