Generating Random Numbers and Strings in Oracle

Generating random numbers is required when there is a need to create a lot of data for testing purposes, or when we simply need to use a number to temporarily tag a process. It may also be necessary to generate random password strings of a fixed size--a very common requirement for websites that create and maintain logins for users.

Whatever the need, the fact is that Oracle provides us with a random number generator. This option is faster than writing your own random generation logic in PL/SQL as Oracle's internal processing logic is used. In addition, it can also be used to generate both character and alphanumeric strings.

DBMS_RANDOM package

The DBMS_RANDOM package will generate random data in character, numeric or alphanumeric formats. The size and the range from which to pickup the random values can also be specified. This package is created by the scriptdbmsrand.sql available in the /rdbms/admin directory.

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