PL/SQL: Generate random values

When you need random values in the Oracle-world you can get them by using the package DBMS_RANDOM. If not already there, you can install the package with the script “dbmsrand.sql” in “ORACLE_HOME/RDBMS/ADMIN”.

The package offers the two functions VALUE and STRING to generate random values for numbers, strings or mixed cases.

When called without any parameter DBMS_RANDOM.VALUE delivers values between 0 and 1:

select dbms_random.value num from dual connect by level <5;

       NUM
----------
0,44323453888163585343305347384952888762
0,27061509862121968713431350682787319082
0,50719289299767089592254463623923727779
0,42666962176478158926225395583206159572

The “connect by level <5" can be omitted as it’s used here just to present more than one example-row.

You can also define a range of values from which to get random values (here it is the range from “1 <= x < 100"):

select dbms_random.value(1,100) num from dual connect by level <5;
       NUM
----------
74,68117799104242371065908850142160589183
25,20810462171063688648794689871086371162
64,05205571240469040169889477158705792932
77,16423950749394682603580224777136966975

To get rid of the fractional digits you can ROUND the values:

select round(dbms_random.value(1,100),0) num from dual connect by level <5;
       NUM
----------
34
81
35
72

Keep in mind, that the generated values are always BELOW the upper bound (it is NOT included in the range).

To limit the result to even numbers you could wrap it with modulo:

select * from (
select round(dbms_random.value(1,100),0) num from dual connect by level <5
)
where mod(num,2)=0;

To just get odd numbers you have to set “mod(num,2)=1”. But don’t trust that this example will get you ALWAYS a number or a constant number of rows! The modulo is just checked against the result-set of the inner query. If that result-set doesn’t contain a even (or odd) number, it will return nothing.

For random strings there are a few options:

 'u', 'U' - returning string in uppercase alpha characters
 'l', 'L' - returning string in lowercase alpha characters
 'a', 'A' - returning string in mixed case alpha characters
 'x', 'X' - returning string in uppercase alpha-numeric characters
 'p', 'P' - returning string in any printable characters.
select dbms_random.string('u',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
QHQPNZTPMJ
HNHFPOKTXU
CGDQFAKMSA
OMOJDCBQVZ
select dbms_random.string('l',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
fdoeswqqpe
zkicmviacg
zhbbtkqjwl
npwmzvcuhu
select dbms_random.string('a',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
hAVpQccUja
UUwcLqQEZB
rsVpKjKNKx
EJKyUflprQ
select dbms_random.string('x',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
6DV2519VE8
D2XV0YJ7JK
A58FMX7FWS
LIEZB4BBX4
select dbms_random.string('p',10) rndvalue from dual connect by level <5;

RNDVALUE
----------
%VttvY &F@
k33]'bp7fQ
V)w6Nno"sO
7'7p62:'zp

DBMS_RANDOM documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_random.htm#sthref4646

See here how to build an odd/even-random-number-generator:
PL/SQL: Building an odd/even-random-number-generator

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: