The following functions present in the package can be used to serve the purpose of generating random numbers and strings.
SEED
The SEED procedure allows you to seed the pseudo-random number generator, making it more random. In Oracle 9i, it was limited to binary integers, but from 10gR1 onward the seed can be either binary integers or strings up to 2000 characters. If you want to consistently generate the same set of pseudo-random numbers, always use the same seed.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('Run 1 : seed=0');
DBMS_RANDOM.seed (val => 0);
FOR i IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
END LOOP;
DBMS_OUTPUT.put_line('Run 2 : seed=0');
DBMS_RANDOM.seed (val => 0);
FOR i IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
END LOOP;
END;
/
Run 1 : seed=0
i=1 : value=1.57028721259217082751060169361419113552
i=2 : value=8.45613845339817447016228976539862457199
i=3 : value=3.0863828054628121078698483286311518089
i=4 : value=2.96455846160836864671401359493438801563
i=5 : value=4.33143708021018476392886232387371374789
Run 2 : seed=0
i=1 : value=1.57028721259217082751060169361419113552
i=2 : value=8.45613845339817447016228976539862457199
i=3 : value=3.0863828054628121078698483286311518089
i=4 : value=2.96455846160836864671401359493438801563
i=5 : value=4.33143708021018476392886232387371374789
PL/SQL procedure successfully completed.
SQL>
If you want to be "more" random, then use a seed that is more unique, like a timestamp.
SET SERVEROUTPUT ON
DECLARE
l_seed VARCHAR2(100);
BEGIN
l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');
DBMS_OUTPUT.put_line('Run 1 : seed=' || l_seed);
DBMS_RANDOM.seed (val => l_seed);
FOR i IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
END LOOP;
l_seed := TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF');
DBMS_OUTPUT.put_line('Run 2 : seed=' || l_seed);
DBMS_RANDOM.seed (val => TO_CHAR(SYSTIMESTAMP,'YYYYDDMMHH24MISSFFFF'));
FOR i IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
END LOOP;
END;
/
Run 1 : seed=20110712191343169029000169029000
i=1 : value=6.92856839447794366531250911463757099898
i=2 : value=8.47244537287144468516381364082381009925
i=3 : value=4.08470375717661625644262354270334730064
i=4 : value=2.98508944622570032931609974281746770627
i=5 : value=1.19036741851059143073794786605451344498
Run 2 : seed=20110712191343170755000170755000
i=1 : value=4.71780531121809498287325559974587576647
i=2 : value=2.29344937809042787674469278814535929363
i=3 : value=6.58595572102475512893934366904993904004
i=4 : value=8.11927492868440287571513126155423300604
i=5 : value=4.54250357876849070353926583794655291077
PL/SQL procedure successfully completed.
SQL>
VALUE
The VALUE function is used to produce random numbers with a specified range. When called without parameters it produce a number greater than or equal to 0 and less than 1, with 38 digit precision.
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('value= ' || DBMS_RANDOM.value);
END LOOP;
END;
/
value= .60580123582956143922768107284146673817
value= .30743163543500648010476130974723317619
value= .07371769421050557513591192974759844853
value= .75944996867333900612723894585372728382
value= .81187104800882163823895225885584477007
PL/SQL procedure successfully completed.
SQL>
If the parameters are used, the resulting number will be greater than or equal to the low value and less than the high value, with the precision restricted by the size of the high value.
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('value(1,100)= ' || DBMS_RANDOM.value(1,100));
END LOOP;
END;
/
value(1,100)= 22.11683652311852179878254011435633450156
value(1,100)= 60.97650098967378711983251359728525219059
value(1,100)= 74.21154250958397305956956920294410867342
value(1,100)= 2.83810490288555600191974686195159201221
value(1,100)= 1.82806520389696996150021012937913228388
PL/SQL procedure successfully completed.
SQL>
Use TRUNC or ROUND to alter the precision as required.
STRING
The STRING function returns a string of random characters of the specified length. The OPT parameter determines the type of string produced as follows:
- 'u', 'U' - uppercase alpha characters
- 'l', 'L' - lowercase alpha characters
- 'a', 'A' - mixed case alpha characters
- 'x', 'X' - uppercase alpha-numeric characters
- 'p', 'P' - any printable characters
The LEN parameter, not surprisingly, specifies the length of the string returned.
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
END LOOP;
END;
/
string('x',10)= BL69189JC0
string('x',10)= XKSI33Z5E8
string('x',10)= WMK7LWIXK7
string('x',10)= E9T9KAZTIX
string('x',10)= 5NTMSELFXD
PL/SQL procedure successfully completed.
SQL>
Combine the STRING and VALUE functions to get variable length strings.
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('string(''L'',?)= ' || DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,21))));
END LOOP;
END;
/
string('L',?)= njpfxnreqlrveh
string('L',?)= wuipbdugwsaeqnh
string('L',?)= lyuqeiytylnickeskdaq
string('L',?)= tphfktvluqqpfhzn
string('L',?)= hufvxdoyyhwa
PL/SQL procedure successfully completed.
SQL>
NORMAL
The NORMAL functions returns random numbers in a normal distribution.
SET SERVEROUTPUT ON
BEGIN
FOR cur_rec IN 1 ..5 LOOP
DBMS_OUTPUT.put_line('normal= ' || DBMS_RANDOM.normal);
END LOOP;
END;
/
normal= .5060599432518892039880357106833452340238
normal= -.5204461674553663724894041142407123011427
normal= -.2850434850053250223307536685373585074784
normal= .4968277078005383563734278996826277189916
normal= -1.1462080711511582757749658225445100209
PL/SQL procedure successfully completed.
SQL>
RANDOM
In Oracle 9i the DBMS_RANDOM package was a little limited, having only the RANDOM procedure to produce random numbers. Added to that, it was necessary to initialize and terminate the random number generator.
SET SERVEROUTPUT ON
DECLARE
l_seed BINARY_INTEGER;
BEGIN
l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
DBMS_RANDOM.initialize (val => l_seed);
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('random= ' || DBMS_RANDOM.random);
END LOOP;
DBMS_RANDOM.terminate;
END;
/
random= 38211913
random= 606582287
random= 1594550431
random= 1795324276
random= -1243085163
PL/SQL procedure successfully completed.
SQL>
From Oracle 10g Release 1 onward, initialization and termination were no longer necessary as calls to DBMS_RANDOM automatically initialize the seed using the date.
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('random= ' || DBMS_RANDOM.random);
END LOOP;
END;
/
random= -1882795818
random= 1556047321
random= 455253988
random= -1611493043
random= 1796172360
PL/SQL procedure successfully completed.
SQL>
Oracle 10g introduced a number of functions that should be used in place of the RANDOM function. In Oracle 11gR1, the RANDOM function was deprecated in favor of these other functions.
Generating Random Dates
There are no specific functions for generating random dates, but we can add random numbers to an existing date to make it random. The following example generates random dates over the next year.
SET SERVEROUTPUT ON
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)));
END LOOP;
END;
/
date= 16-APR-2010 00:00:00
date= 20-JUN-2010 00:00:00
date= 21-MAY-2010 00:00:00
date= 25-JUL-2010 00:00:00
date= 23-JAN-2010 00:00:00
PL/SQL procedure successfully completed.
SQL>
By doing the correct divisions, we can add random numbers of hours, seconds or minutes to a date.
SET SERVEROUTPUT ON
DECLARE
l_hours_in_day NUMBER := 24;
l_mins_in_day NUMBER := 24*60;
l_secs_in_day NUMBER := 24*60*60;
BEGIN
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('hours= ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_hours_in_day)));
END LOOP;
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('mins = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_mins_in_day)));
END LOOP;
FOR i IN 1 .. 5 LOOP
DBMS_OUTPUT.put_line('secs = ' || (TRUNC(SYSDATE) + (TRUNC(DBMS_RANDOM.value(0,1000))/l_secs_in_day)));
END LOOP;
END;
/
hours= 30-DEC-2010 21:00:00
hours= 09-DEC-2010 23:00:00
hours= 25-DEC-2010 08:00:00
hours= 30-DEC-2010 06:00:00
hours= 07-DEC-2010 20:00:00
mins = 07-DEC-2010 11:59:00
mins = 07-DEC-2010 11:37:00
mins = 07-DEC-2010 14:32:00
mins = 07-DEC-2010 05:14:00
mins = 07-DEC-2010 15:45:00
secs = 07-DEC-2010 00:12:33
secs = 07-DEC-2010 00:12:26
secs = 07-DEC-2010 00:10:26
secs = 07-DEC-2010 00:10:35
secs = 07-DEC-2010 00:13:14
PL/SQL procedure successfully completed.
test@db11g>
PL/SQL procedure successfully completed.
SQL>
Generating Random Data
The DBMS_RANDOM package is useful for generating random test data. You can generate large amounts quickly by combining it into a query.
CREATE TABLE random_data (
id NUMBER,
small_number NUMBER(5),
big_number NUMBER,
short_string VARCHAR2(50),
long_string VARCHAR2(400),
created_date DATE,
CONSTRAINT random_data_pk PRIMARY KEY (id)
);
INSERT /*+ APPEND */ INTO random_data
SELECT level AS id,
TRUNC(DBMS_RANDOM.value(1,5)) AS small_number,
TRUNC(DBMS_RANDOM.value(100,10000)) AS big_number,
DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS short_string,
DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(100,400))) AS long_string,
TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date
FROM dual
CONNECT BY level <= 10000;
COMMIT;
Source: http://oracle-base.com/articles/misc/dbms_random.php
