PL/SQL - DBMS_RANDOM to generate random number, string and date

Purpose

Random is useful in many cases (for instance when we need to generate data for testing purposes)

Articles Related

Set up

This package is created by the script dbmsrand.sql available in the

gerardnico@orcl>connect sys/password@ORCL as sysdba
Connected.
sys@orcl>@?/rdbms/admin/dbmsrand
Package created.
Package body created.
Synonym created.
Grant succeeded.

DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, userid, and process id if no explicit initialization is performed.

In some cases, such as when testing, you may want the sequence of random numbers to be the same on every run. In that case, you seed the generator with a constant value by calling one of the overloads of DBMS_RANDOM.SEED.

dbms_random.seed('What you want');

To produce different output for every run, simply to omit the call to “Seed” and the system will choose a suitable seed for you.

gerardnico@orcl>select dbms_random.random from dual;

    RANDOM
==========
-748908608

The function VALUE generate random numbers from the range provided. The range will be taken as 0-1 if none is provided.

gerardnico@orcl>select round(dbms_random.value(1999,2007)) num from dual;

       NUM
==========
      2001

The function STRING generate strings in upper case, lower case or alphanumeric format.

   DBMS_RANDOM.STRING
   opt  IN  CHAR,
   len  IN  NUMBER)
  RETURN VARCHAR2;

Opt specifies what the returning string looks like:

• 'u', 'U' - returning string in uppercase alpha characters
• 'l', 'L' - returning string in lowercase alpha characters
• 'a', 'A' - returning string in mixed case alpha characters
• 'x', 'X' - returning string in uppercase alpha-numeric characters
• 'p', 'P' - returning string in any printable characters.

Otherwise the returning string is in uppercase alpha characters.

gerardnico@orcl>select dbms_random.string('A', 10) str from dual;

STR
==========
eYksMvGhTT

Oracle stores dates as integer offsets from a key date in the past (January 1, 4712 B.C., in case you were curious). This means that you can generate random dates in a given range by finding the integer that corresponds to your desired start date, and then adding a random integer to it.

You can generate the internal date number for today's date by using the TO_CHAR function with the 'J' format code:

SELECT TO_CHAR(SYSDATE, 'J') FROM DUAL;

For example, to generate random dates during the year 2003, you would first determine the date integer for January 1, 2003:

gerardnico@orcl>SELECT TO_CHAR(TO_DATE('01/01/08','mm/dd/yy'),'J') FROM DUAL;

TO_CHAR
=======
2454467

The system responds with 2454467. So, to generate a random date within the year, we use DBMS_RANDOM.VALUE with :

• a low_value of 2454467
• and a high_value of 2454467 + 364 day by year,

and convert it to a date.

gerardnico@orcl>SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454467,2454467+364)),'J') FROM DUAL;

TO_DATE(T
=========
04-JUN-08

• Using DBMS_RANDOM
• Use DBMS_RANDOM to generate text and date values