Database.ca SQL Server Blog

My Blog

Thursday, March 13 2014

Keywords: Oracle, PL/SQL, PL-SQL, Random, Randomizer, Seed, Value, Normal, Generating Random Dates, Random Data, DBMS_RANDOM

Generate random date value in Oracle (PL/SQL)

My team and I were running a series of performance tests on a then soon-to-be-beta-release data warehouse application and we noticed that we needed to randomize the 'last synched date' field in one of the tables...in order to be able to stop the long-lasting tests at any time before it completes...and still get meaningful numbers such as "average time it took to process 1000 products" etc. The problem was that if the date values were not random, then a batch of 1000 with a certain date would take 0 second to process...and another batch much longer. The end pool needed to be homogenously mixed.

 

Prior to Oracle 11g, DBMS_RANDOM.random was used. You are better off using the new DBMS_RANDOM.value(lowerlimit,higherlimit)...why? No need to initialize or terminate DBMS_RANDOM package like before...and most importantly it now automatically initializes the SEED value using a date...so you get a real random value and do not need to reset the seed by yourself any longer.

 

There are no specific PL/SQL functions for generating random dates per se, but we can easily set the lowerlimit and higherlimit parameter values of DBMS_RANDOM.value(lowerlimit,higherlimit) function to valid Julian day values , and then we convert the string back to a Julian date type, to get a random date between X and Y.

 

Julian day, is the number of days since January 1, 4712 BC.

 

I ran the following script to create random date values between 01/01/2000 and today:

 

Oracle, PL/SQL, PL-SQL, Random, Randomizer, Seed, Value, Normal, Generating Random Dates, Random Data, DBMS_RANDOM

update
  PRODUCTS
set
  LASTSYNCHDATE = TO_DATE(
   TRUNC(
   DBMS_RANDOM.value(
   TO_CHAR(date '2000-01-01','J')
   ,TO_CHAR(SYSDATE,'J')
   )
  ),'J'
  )
  ;
commit;

 

Oracle, PL/SQL, PL-SQL, Random, Randomizer, Seed, Value, Normal, Generating Random Dates, Random Data, DBMS_RANDOM

Note that you could also replace SYSDATE by LASTSYNCHDATE, to make the upper limit the same as the existing value, if and only if LASTSYNCHDATE does not contain NULL value records in the database.

 

It took less than a second to run on a table of million records and it created truly random values, thus providing us with the heterogeneously mixed values we required for our tests.

 

 

 

Written by Ramin Haghighat