One of the best builtin packages in Oracle for anyone who needs to create random data is DBMS_RANDOM. Not only can you use it to create random values, but you can also use it to make random selections. For instance, if you have a list of values that you want to assign randomly, you can weight the chances however you want and let the randomizer do the work for you.

DBMS_RANDOM.VALUE

The Value function returns a random number. The default number that it returns is between 0 and 1 and has 38 digits to the right of the decimal. If you wanted, you could round it, take a portion of it, multiply it, etc. You can also just tell it, give me a number between X and Y.

Default VALUE

As you can see, the number is certainly useable. We could multiply it by 100, and round it to 0 positions. Not bad.

SQL> select dbms_random.value from dual;

 VALUE
----------
 .97034446


SQL> select round(dbms_random.value*100,0) from dual;

ROUND(DBMS_RANDOM.VALUE*100,0)
------------------------------
 57

Low and High

We can also provide a low number and a high number. Obviously, the range is now completely up to us. It will still need to be rounded as the number still has 38-bit precision.

We’ll just make this a little nerdy and make them dice rolls from a D&D campaign. That last roll might be a crit!

SQL> select round(dbms_random.value(1,4)) d4 from dual;

 D4
----------
 3

SQL> select round(dbms_random.value(1,6)) d6 from dual;

 D6
----------
 4

SQL> select round(dbms_random.value(1,8)) d8 from dual;

 D8
----------
 5

SQL> select round(dbms_random.value(1,12)) d12 from dual;

 D12
----------
 4

SQL> select round(dbms_random.value(1,20)) d20 from dual;

 D20
----------
 19

Randomly Assign Data

Now, on to using this to randomly assign data. We have a lookup table with an ID. We have values 1 through 19.

First, we’ll set up some tables and data.

drop table tbl_locations cascade constraints;

create table tbl_locations (
 location_id integer primary key,
 city varchar2(50),
 state varchar2(2),
 zip_code varchar2(10)
 );

insert into tbl_locations values (1,'ORLANDO','FL', '32801');
insert into tbl_locations values (2,'ORLANDO','FL', '32802');
insert into tbl_locations values (3,'ORLANDO','FL', '32803');
insert into tbl_locations values (4,'ORLANDO','FL', '32804');
insert into tbl_locations values (5,'ORLANDO','FL', '32805');
insert into tbl_locations values (6,'ORLANDO','FL', '32806');

create table tbl_offices (
 office_id integer not null,
 name varchar2(20) not null,
 location_id integer references sys.tbl_locations (location_id));

Now, we’ll populate tbl_offices. It’s test data, so just pick something from 1-6 for the location_id.

SQL> insert into tbl_offices select 1, 'HQ', round(dbms_random.value(1,6)) from dual;

1 row created.

SQL> select * from tbl_offices;

 OFFICE_ID NAME                 LOCATION_ID
---------- -------------------- -----------
 1         HQ                   4
1 row selected.

Let’s say we need more of these and we want to cook up an anonymous block to insert random data. Run this and check out the serveroutput. The results of the string concatenation with the random value is inserted with the execute immediate command.

declare
 strSQL varchar2(60);
begin

  for i in 1..10 loop
    strSQL := 'insert into tbl_offices values (' 
      || to_char(i+1) 
      || ','' Location ' || to_char(i) || ''',' 
      || to_char(round(dbms_random.value(1,6))) || ')';
     dbms_output.put_line(strSQL);
     execute immediate(strSQL);
 end loop;

end;

SQL> select * from tbl_offices;

 OFFICE_ID NAME        LOCATION_ID
---------- ----------- -----------
 1         HQ          4
 2         Location 1  5
 3         Location 2  6
 4         Location 3  2
 5         Location 4  6
 6         Location 5  6
 7         Location 6  3
 8         Location 7  2
 9         Location 8  4 
 10        Location 9  2
 11        Location 10 4
11 rows selected.

Let’s do an update, now. I want a large chance to be given for an office to be at location 1, so I’m going to weight the chances in that direction. So, let’s give location 1 a 50% chance. Everything else has a 10% chance. We get there by doing a little reverse thinking with the numbers. Assign the random values 1 through 5 to assign 2 through 6 as location_id. Everything else – 6 through 10 – returns a value of 1 for location_id.

 update tbl_offices
 set location_id = CASE round(dbms_random.value(1,10))
 WHEN 1 THEN 2
 WHEN 2 THEN 3
 WHEN 3 THEN 4
 WHEN 4 THEN 5
 WHEN 5 THEN 6
 ELSE 1
 END
 /

 select location_id, count(*) from tbl_offices group by location_id;

LOCATION_ID COUNT(*)
----------- ----------
 1          11
 6          2
 2          2
 5          2
 4          2
 3          2

6 rows selected.

DBMS_RANDOM.STRING

Now let’s say we want to generate some alpha data for our test table instead of Location 1, Location 2. We can use the String function. There are two arguments for string.

1. Options. ‘u’ = upper alpha, ‘l’ = lower alpha, ‘a’ = mixed alpha ‘x’ = mixed alphanumeric, ‘p’ = any printable.
2. Length. How long would you like your random string, ma’am and/or sir?

SQL> select dbms_random.string('u',10) from dual;

DBMS_RANDOM.STRING('U',10)
--------------------------------------------------

DXRTLHWBNY

Combine the two for more random data fun!

SQL> select dbms_random.string('u',round(dbms_random.value(5,10))) from dual;

DBMS_RANDOM.STRING('U',ROUND(DBMS_RANDOM.VALUE(5,10)))
--------------------------------------------------------------------------------

IJLPBCFIGV

SQL> select dbms_random.string('u',round(dbms_random.value(5,10))) from dual;

DBMS_RANDOM.STRING('U',ROUND(DBMS_RANDOM.VALUE(5,10)))
--------------------------------------------------------------------------------

XOEGBL

The Coup d’Gras

Now, we will combine these functions to insert more records into our tbl_offices table. The changes I made:

• Add a cnt variable.
• Populated cnt variable with count of records from tbl_offices.
• Set for loop to start at cnt+1 and end at cnt+3. Now my ids will be right.
• Added a random string for the location in UPPER alpha characters in a length of 5 to 10.

declare
 strSQL varchar2(60);
 cnt integer;
begin

 select count(*) into cnt from tbl_offices;

 for i in cnt+1 ..cnt+3 loop
 strSQL := 'insert into tbl_offices values (' 
 || to_char(i+1) 
 || ',''' || dbms_random.string('u',round(dbms_random.value(5,10))) || ''',' 
 || to_char(round(dbms_random.value(1,6))) || ')';
 dbms_output.put_line(strSQL);
 execute immediate(strSQL);
 end loop;

end;

select * from tbl_offices where office_id between 23 and 25;

 OFFICE_ID NAME        LOCATION_ID
---------- ----------- -----------
 23        JQNKQZX     4
 24        OESGR       5
 25        EKGZPCC     2

3 rows selected.

There are more functions to DBMS_RANDOM that you can check out in the link provided throughout this article or here.