DBMS_RANDOM : Generating Random Data (Numbers, Strings and Dates) in Oracle

Sharing buttons:

hello again this is Tim from Oracle base


today we're going to be talking about

generating random data using the DBMS

random package

the first thing we need to do is decide

if we want to seed the random number

generator in this block of code we set

the seed to a value of zero and then

loop through five times calling the

value function we then reset the seed

and loop again

we can see from the output that we get

the same five numbers generated each

time this can be quite useful in testing

where we want to do repeated runs but

make sure those runs are comparable

in this example we use a time stamp to

create a unique string and then use that

in the call to the seed procedure in

this way each rung gets a unique seed

the output shows we've got unique values

in each run we didn't really need to

call the seed procedure a second time

without it we would have just got more

random numbers anyway

the value function works in two ways

without any parameters it returns a

fraction it can also accept high and low

values and produce a random number

between that range if you want a range

of integers

remember to trunk the return value and

add one to the upper boundary

the string function returns a random

string of the specified length the first

parameter indicates the format of the

string u means uppercase L means

lowercase a means mixed alpha X means

alphanumeric in uppercase and P means

any principal character

combining the string and value functions

allows us to produce random length


the normal function returns random

numbers in a normal distribution

we can generate random dates by adding

or subtracting random numbers from dates

if we do the appropriate division we can

also alter hours minutes or seconds


to pull this all together let's create a

table to hold a variety of number string

and date columns

using the functions we've described

previously we can easily populate it

with a variety of number string and date


thanks for watching be sure to check out

the links in the description box and

subscribe to the channel and like the

video if you liked it