DBMS_RANDOM to generate random numbers and strings

Introduction

 

Oracle offers a free to use Oracle Express Edition software  if one wants to get the database software + starter database (XE) + sample tables  in one easy installation method.  The sample tables are owned by the HR schema and some of these include EMPLOYEES and JOBS  tables.  But the number of records in these sample tables are very small and hence may not be suitable to practice SQL and PL/SQL that include large data. Sure, one may add more records to these tables by executing INSERT statements. But this can be tedious if one wants to generate hundreds of thousands of records for data processing.

DBMS_RANDOM package

 

Among the many supplied PL/SQL packages by Oracle, DBMS_RANDOM is one that can be leveraged to generate random data. One can use a PL/SQL iterative control structure such as the FOR Loop to quickly fill a table with hundreds of thousands of records. The below FOR loop can quickly load 400000 records into the emp table (400001 to be precise !)

create table emp (emp_name varchar2(20),emp_number number);

begin

for i in 100000..500000 loop
 insert into emp values(dbms_random.string('U',20),round(dbms_random.value(40000,400000)));
 end loop;
end;
 /
 

DBMS_RANDOM has many procedures and functions and the above FOR loop illustrates the use of the STRING function and VALUE function. The above string function accepts two parameters with the first asking the function to return Upper case string value ('U') and the second parameter value 20 asking the function to return 20 length character strings. The other VALUE function returns a random number that is between the first parameter 40000 and the second parameter 400000

Conclusion

 

If one wants to gain advanced skills such as knowing about BULK PROCESSING operations (BULK COLLECT, FORALL etc.) in PL/SQL, one should work with big tables with huge amounts of data. DBMS_RANDOM package can be quite handy in quicking generating a big data set in Oracle tables for practice work.