8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » Misc » Here

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

The DBMS_RANDOM package provides an API for the pseudo-random number generator.

These are pseudo-random numbers, which are fine for generating test data. If you need truly random numbers, the DBMS_RANDOM package is not the correct choice.

Related articles.

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.

From 10g onward you don't need to explicitly call SEED. In Oracle 10g it is automatically seeded with the date. In later releases it is seeded using the date, user ID, and process ID.

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. For example, to produce random integer values between 1 and 10 truncate the output and add 1 to the upper boundary.

TRUNC(DBMS_RANDOM.value(1,11))

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:

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 function 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 favour 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.

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;

Large Volumes of Data

A simple way to generate rows of data is to use the CONNECT BY clause, with the ROWNUM or LEVEL pseudo-columns providing a row number and the number of rows produced determined by the CONNECT BY clause..

select level
from   dual
connect by level <= 5;

     LEVEL
----------
         1
         2
         3
         4
         5

SQL>

select rownum
from   dual
connect by level <= 5;

    ROWNUM
----------
         1
         2
         3
         4
         5

SQL>

Above a certain size you are going to hit memory issues.

select level
from   dual
connect by level < 1000000;

     LEVEL
----------
         1
         2
...
     89234
     89235
ERROR:
ORA-30009: Not enough memory for CONNECT BY operation

89235 rows selected.

SQL>

To generate more rows, use a WITH clause and materialize the results as a global temporary table, then use that to create a cartesian product. You can control the number of rows returned using ROWNUM.

with data as (
  select /*+ materialize */ level as id
  from   dual
  connect by level <= 10000
)
select rownum as id
from   data, data, data
where  rownum <= 1000000;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.