codemonth.dk

One project every month - making stuff better ...

Generating random data

We all need to create random data every now and again. Most of the times I have resolved to simply using dbms_random to generate random strings and numbers. The problem with approach though, is that data does not look like "real" data, and indexing will not be realistic. The Alexandria library contains a package that has a few functions random_util_pkg, but it is limited in the type of data you can create, and I felt that there were a couple of more parameters where I needed a bit more control on the output.

So I have created RANDOM_NINJA which for now has the basic functions that I needed.

Just see the quick demo I have created below:

First I install the code using the NPG package manager:


[oracle@localhost cli]$ ./npg install random_ninja t/t
NPG Install

Starting installation of: random_ninja
Session user is: T
Package: random_ninja ready to be installed.
Downloading NPG file.
Unpacking npg zip file.
Validating NPG requirements.
All NPG requirements validated.
Compiling sources.
Sources compiled without errors.
1 NPG installed successfully.

[oracle@localhost cli]$

When the source is installed, the following packages will be available to use:

  • core_random - The core random generator, for numbers and characters.
  • text_random - A package to generate random text, that will act and behave like real words and sentences and paragraphs.
  • time_random - Generate any random date or time component or full date or timestamp, with full flexibility
  • person_random - Random basic information about a person, such as age, name, birthdate etc. All with the same flexibility as the other packages.

So let us take a look at some of the functions available in each package

The core package has just that. Core random functionality. Like a random boolean value or random character or string, and each with customized parameters to cater for most requirements. Here is a quick demo:

[oracle@localhost cli]$ sqlplus t/t

SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 1 06:48:27 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sat Oct 01 2016 05:57:29 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set serverout on
SQL> begin
  2  if core_random.r_bool then
  3  dbms_output.put_line('True');
  4  else
  5  dbms_output.put_line('False');
  6  end if;
  7  end;
  8  /
True

PL/SQL procedure successfully completed.

SQL> /
True

PL/SQL procedure successfully completed.

SQL> /
False

PL/SQL procedure successfully completed.

SQL> select core_random.r_natural from dual;

 R_NATURAL
----------
2.1240E+15

SQL> set numwidth 30
SQL> r
  1* select core_random.r_natural from dual

		     R_NATURAL
------------------------------
	      7592341962856936

SQL> select core_random.r_natural(1,20) from dual;

   CORE_RANDOM.R_NATURAL(1,20)
------------------------------
			    17

SQL> select core_random.r_character from dual;

R_CHARACTER
--------------------------------------------------------------------------------
.

SQL> r
  1* select core_random.r_character from dual

R_CHARACTER
--------------------------------------------------------------------------------
4

SQL> r
  1* select core_random.r_character from dual

R_CHARACTER
--------------------------------------------------------------------------------
}

SQL> select core_random.r_character('abcde') from dual;

CORE_RANDOM.R_CHARACTER('ABCDE')
--------------------------------------------------------------------------------
c

SQL> r
  1* select core_random.r_character('abcde') from dual

CORE_RANDOM.R_CHARACTER('ABCDE')
--------------------------------------------------------------------------------
b

SQL> select core_random.r_float from dual;

		       R_FLOAT
------------------------------
	 1482159639127345.9918

SQL> select core_random.r_float(3, 10, 20) from dual;

  CORE_RANDOM.R_FLOAT(3,10,20)
------------------------------
			 15.57

SQL> r
  1* select core_random.r_float(3, 10, 20) from dual

  CORE_RANDOM.R_FLOAT(3,10,20)
------------------------------
			19.383

SQL> r
  1* select core_random.r_float(3, 10, 20) from dual

  CORE_RANDOM.R_FLOAT(3,10,20)
------------------------------
			 10.75

SQL> r
  1* select core_random.r_float(3, 10, 20) from dual

  CORE_RANDOM.R_FLOAT(3,10,20)
------------------------------
			18.045

SQL> select core_random.r_string from dual;

R_STRING
--------------------------------------------------------------------------------
*3Eh*XojoM5*J,K

SQL> select core_random.r_string(5) from dual;

CORE_RANDOM.R_STRING(5)
--------------------------------------------------------------------------------
D58iS

SQL> select core_random.r_string(5,'abchjk') from dual;

CORE_RANDOM.R_STRING(5,'ABCHJK')
--------------------------------------------------------------------------------
bjabc

SQL> r
  1* select core_random.r_string(5,'abchjk') from dual

CORE_RANDOM.R_STRING(5,'ABCHJK')
--------------------------------------------------------------------------------
jhjcb

SQL> r
  1* select core_random.r_string(5,'abchjk') from dual

CORE_RANDOM.R_STRING(5,'ABCHJK')
--------------------------------------------------------------------------------
ajkhh

SQL> 

Getting random text is easy. But getting random text that kinda looks like real text, and that will work the same way is always something you need. The text_random package gives you just that:

SQL> select text_random.r_syllable from dual;

R_SYLLABLE
--------------------------------------------------------------------------------
jo

SQL> r
  1* select text_random.r_syllable from dual

R_SYLLABLE
--------------------------------------------------------------------------------
diw

SQL> select text_random.r_word(2) from dual;

TEXT_RANDOM.R_WORD(2)
--------------------------------------------------------------------------------
hotit

SQL> select text_random.r_word(3) from dual;

TEXT_RANDOM.R_WORD(3)
--------------------------------------------------------------------------------
letepko

SQL> select text_random.r_word(4) from dual;

TEXT_RANDOM.R_WORD(4)
--------------------------------------------------------------------------------
bolurmezre

SQL> select text_random.r_sentence(4) from dual;

TEXT_RANDOM.R_SENTENCE(4)
--------------------------------------------------------------------------------
Tov fadug dijo koszuh.

SQL> select text_random.r_sentence(6) from dual;

TEXT_RANDOM.R_SENTENCE(6)
--------------------------------------------------------------------------------
Ke row sezvo pu cavpotte widen.

SQL> r
  1* select text_random.r_sentence(6) from dual

TEXT_RANDOM.R_SENTENCE(6)
--------------------------------------------------------------------------------
Gekek pukegus pewpop hi giwlo reces.

SQL> select text_random.r_sentence(8) from dual;

TEXT_RANDOM.R_SENTENCE(8)
--------------------------------------------------------------------------------
Wismit ti femis melmom jowo fukimom re hifdicte.

SQL> select text_random.r_paragraph(3) from dual;

TEXT_RANDOM.R_PARAGRAPH(3)
--------------------------------------------------------------------------------
Heffe remkovi viji zelevre goci wodi sup ke petwos zupjom ne didwo lukbafsu. Lin
wakot soponri pelkoj tejde rorah silhu cerjef dovez wito tatgo lagli senlohjo nu
velpan. Vero jukho wotosom ma ve fiwotko cefi deplu ronsisvo pojtu.


SQL> select text_random.r_paragraph(5) from dual;

TEXT_RANDOM.R_PARAGRAPH(5)
--------------------------------------------------------------------------------
Gogmek jet cup wutewu jale vela diwhet perneto lifac ri ku hofum. Jimo pevmu suh
ilne sesigip hezewgic libzeh racmi kefmu mo wirfa fujzi jedgi mirsihok. Selogto
fan lewiwi vegeg tuhcecro wutrop cenhinpo kimirno mas. Bu dofkez sapikwe peffam
kovge lewpiswi rapmuso mo zenwok ziwsori vig kom. Wub rasop hokhe guvokco tuz si
 fofepfot zimimla.


SQL> 

With this package you can build a random component of any part of a date, or complete random dates:

SQL> select time_random.r_millisecond from dual;

		 R_MILLISECOND
------------------------------
			   512

SQL> select time_random.r_second from dual;

		      R_SECOND
------------------------------
			    36

SQL> select time_random.r_minute from dual;

		      R_MINUTE
------------------------------
			    39

SQL> select time_random.r_hour from dual;

			R_HOUR
------------------------------
			    14

SQL> select time_random.r_ampm from dual;

R_AMPM
--------------------------------------------------------------------------------
pm

SQL> r
  1* select time_random.r_ampm from dual

R_AMPM
--------------------------------------------------------------------------------
pm

SQL> r
  1* select time_random.r_ampm from dual

R_AMPM
--------------------------------------------------------------------------------
am

SQL> select time_random.r_year from dual;

			R_YEAR
------------------------------
			  1923

SQL> r
  1* select time_random.r_year from dual

			R_YEAR
------------------------------
			  1942

SQL> select time_random.r_year(1985, 1995) from dual;

 TIME_RANDOM.R_YEAR(1985,1995)
------------------------------
			  1993

SQL> r
  1* select time_random.r_year(1985, 1995) from dual

 TIME_RANDOM.R_YEAR(1985,1995)
------------------------------
			  1990

SQL> select time_random.r_month from dual;

		       R_MONTH
------------------------------
			    11

SQL> r
  1* select time_random.r_month from dual

		       R_MONTH
------------------------------
			     7

SQL> select time_random.r_month('summer') from dual;

 TIME_RANDOM.R_MONTH('SUMMER')
------------------------------
			     7

SQL> r
  1* select time_random.r_month('summer') from dual

 TIME_RANDOM.R_MONTH('SUMMER')
------------------------------
			     7

SQL> r
  1* select time_random.r_month('summer') from dual

 TIME_RANDOM.R_MONTH('SUMMER')
------------------------------
			     7

SQL> r
  1* select time_random.r_month('summer') from dual

 TIME_RANDOM.R_MONTH('SUMMER')
------------------------------
			     7

SQL> select time_random.r_month('spring') from dual;

 TIME_RANDOM.R_MONTH('SPRING')
------------------------------
			     3

SQL> r
  1* select time_random.r_month('spring') from dual

 TIME_RANDOM.R_MONTH('SPRING')
------------------------------
			     4

SQL> select time_random.r_day from dual;

			 R_DAY
------------------------------
			     8

SQL> r
  1* select time_random.r_day from dual

			 R_DAY
------------------------------
			     3

SQL> r
  1* select time_random.r_day from dual

			 R_DAY
------------------------------
			    11

SQL> select time_random.r_epoch from dual;

		       R_EPOCH
------------------------------
		 1132973770899

SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select time_random.r_date from dual;

R_DATE
--------------------
12-AUG-1982 10:30:23

SQL> r
  1* select time_random.r_date from dual

R_DATE
--------------------
27-NOV-1967 11:03:47

SQL> select time_random.r_date(1978,02,28) from dual;

TIME_RANDOM.R_DATE(1
--------------------
28-FEB-1978 09:12:13

SQL> r
  1* select time_random.r_date(1978,02,28) from dual

TIME_RANDOM.R_DATE(1
--------------------
28-FEB-1978 06:50:29

SQL> select time_random.r_timestamp from dual;

R_TIMESTAMP
---------------------------------------------------------------------------
12-AUG-84 12.46.33.119000000 PM

SQL>

The person_random package gives us the ability to create realistic random personal information, such as age, birthday, names etc:

SQL> select person_random.r_age from dual;

			 R_AGE
------------------------------
			    59

SQL> select person_random.r_age('child') from dual;

  PERSON_RANDOM.R_AGE('CHILD')
------------------------------
			    10

SQL> r
  1* select person_random.r_age('child') from dual

  PERSON_RANDOM.R_AGE('CHILD')
------------------------------
			     8

SQL> select person_random.r_age('teen') from dual;

   PERSON_RANDOM.R_AGE('TEEN')
------------------------------
			    17

SQL> select person_random.r_age('senior') from dual;

 PERSON_RANDOM.R_AGE('SENIOR')
------------------------------
			    87

SQL> select person_random.r_age('adult') from dual;

  PERSON_RANDOM.R_AGE('ADULT')
------------------------------
			    30

SQL> select person_random.r_birthday from dual;

R_BIRTHDAY
--------------------
31-MAR-1963 02:06:06

SQL> select person_random.r_birthday('child') from dual;

PERSON_RANDOM.R_BIRT
--------------------
28-NOV-2015 16:52:17

SQL>  select person_random.r_birthday('senior') from dual;

PERSON_RANDOM.R_BIRT
--------------------
25-SEP-1940 17:25:03

SQL> select person_random.r_firstname from dual;

R_FIRSTNAME
--------------------------------------------------------------------------------
Alex

SQL> select person_random.r_firstname('US') from dual;

PERSON_RANDOM.R_FIRSTNAME('US')
--------------------------------------------------------------------------------
Austin

SQL> select person_random.r_firstname('US','female') from dual;

PERSON_RANDOM.R_FIRSTNAME('US','FEMALE')
--------------------------------------------------------------------------------
Lily

SQL> select person_random.r_firstname('US','male') from dual;

PERSON_RANDOM.R_FIRSTNAME('US','MALE')
--------------------------------------------------------------------------------
Dylan

SQL> select person_random.r_name from dual;

R_NAME
--------------------------------------------------------------------------------
Connor Lee

SQL> select person_random.r_name('US', 'female') from dual;

PERSON_RANDOM.R_NAME('US','FEMALE')
--------------------------------------------------------------------------------
Lily Taylor

SQL> r
  1* select person_random.r_name('US', 'female') from dual

PERSON_RANDOM.R_NAME('US','FEMALE')
--------------------------------------------------------------------------------
Isabella Sanders

SQL> select person_random.r_gender from dual;

R_GENDER
--------------------------------------------------------------------------------
M

SQL> select person_random.r_prefix from dual;

R_PREFIX
--------------------------------------------------------------------------------
Dr.

SQL> r
  1* select person_random.r_prefix from dual

R_PREFIX
--------------------------------------------------------------------------------
Mrs.

SQL> select person_random.r_prefix('male') from dual;

PERSON_RANDOM.R_PREFIX('MALE')
--------------------------------------------------------------------------------
Dr.

SQL> r
  1* select person_random.r_prefix('male') from dual

PERSON_RANDOM.R_PREFIX('MALE')
--------------------------------------------------------------------------------
Dr.

SQL>

These four packages are currently the ones which are fully functional, but there are others that I am working on as well, which you can find at the random_ninja github repository. For instance I am creating a package to generate random location information such as address or coordinates or altitude. You can find the first draft on that in the location_random package. The list of to-do packages include financial data, web data and more. All of these will be included in the NPG package once they are ready, but if you want to play with them before feel free to grab it directly from the github site.

Tagged in : DBMS_RANDOM, DBMS_SQL, ORDS