DBA Hub

📋Steps in this guide1/8

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

A description of how to generate random numbers, strings and dates using the DBMS_RANDOM package in Oracle.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

SEED

The 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 . In Oracle 10g it is automatically seeded with the date. In later releases it is seeded using the date, user ID, and process ID. If you want to be "more" random, then use a seed that is more unique, like a timestamp.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
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>

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>
2

VALUE

The 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. 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. Use or 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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>

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>

TRUNC(DBMS_RANDOM.value(1,11))
3

STRING

The function returns a string of random characters of the specified length. The 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 parameter, not surprisingly, specifies the length of the string returned. Combine the STRING and VALUE functions to get variable length strings.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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>

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>
4

NORMAL

The function returns random numbers in a normal distribution.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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>
5

RANDOM

In Oracle 9i the package was a little limited, having only the procedure to produce random numbers. Added to that, it was necessary to initialize and terminate the random number generator. From Oracle 10g Release 1 onward, initialization and termination were no longer necessary as calls to automatically initialize the seed using the date. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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>

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>
6

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. By doing the correct divisions, we can add random numbers of hours, seconds or minutes to a date.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
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>

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>
7

Generating Random Data

The package is useful for generating random test data. You can generate large amounts quickly by combining it into a query.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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;
8

Large Volumes of Data

A simple way to generate rows of data is to use the clause, with the or pseudo-columns providing a row number and the number of rows produced determined by the clause.. Above a certain size you are going to hit memory issues. To generate more rows, use a 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 . For more information see: - DBMS_RANDOM : Generating Random Data - DBMS_RANDOM Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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>

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>

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!