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

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - Purpose

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

Advertising

3 - Set up

This package is created by the script dbmsrand.sql available in the <ORACLE_HOME>/rdbms/admin directory and must be installed as SYS.

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.

4 - Number

4.1 - Generating a random number

gerardnico@orcl>SELECT DBMS_RANDOM.random FROM dual;
 
    RANDOM
==========
-748908608

4.2 - Generating a random number between year 1999 and 2007

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

5 - String

5.1 - Generating a random string of 10 characters

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.

[email protected]>SELECT dbms_random.string('A', 10) str FROM dual;
 
STR
==========
eYksMvGhTT
Advertising

6 - Date

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;

6.1 - Generating a random date during the year 2008

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

[email protected]>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.

[email protected]>SELECT TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2454467,2454467+364)),'J') FROM DUAL;
 
TO_DATE(T
=========
04-JUN-08

7 - Reference

Advertising
lang/plsql/dbms_random.txt · Last modified: 2017/09/13 16:04 by gerardnico