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

The DBMS_RANDOM box provides an API for the pseudo-random number generator .
associate articles .

SEED

The SEED procedure allows you to source the pseudo-random act generator, making it more random. In Oracle 9i, it was limited to binary star integers, but from 10gR1 onward the semen can be either binary integers or strings up to 2000 characters. If you want to consistently generate the same fix of pseudo-random numbers, always use the like seed.

From 10g forth you do n’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, drug user ID, and summons 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 serve is used to produce random numbers with a intend range. When called without parameters it produce a number greater than or adequate to 0 and less than 1, with 38 finger preciseness .

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 first gear value and less than the high value, with the preciseness restricted by the size of the high prize .

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

TRUNC(DBMS_RANDOM.value(1,11))

STRING

The STRING function returns a string of random characters of the specify duration. 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 amazingly, specifies the length of the chain 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 distance 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 affair returns random numbers in a convention 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 specify, having lone 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 forth, low-level formatting and termination were no longer necessary as calls to DBMS_RANDOM mechanically 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>

prophet 10g introduced a number of functions that should be used in place of the RANDOM serve. 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 trace exercise generates random dates over the following 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 software is useful for generating random test data. You can generate big amounts promptly by combining it into a question .

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 total 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 irregular table, then practice 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 .