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

Card Puncher Data Processing


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

Set up

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

/rdbms/admin directory and must be installed as SYS.
gerardnico@orcl>connect sys/password@ORCL as sysdba
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.

Generating a random number
gerardnico@orcl>select dbms_random.random from dual;

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;

Generating a random string of 10 characters

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

   opt  IN  CHAR,
   len  IN  NUMBER)

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;


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:

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:

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


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;


Discover More
Card Puncher Data Processing
Oracle Database - EXPLAIN PLAN and Autotrace Comparison

First, I create a partition table and I generate random data with the DBMS_RANDOM package (). We see with : that a full table scan will be performed in the table t the cost for each step (3) ...
Card Puncher Data Processing
PL/SQL - (Procedure Language|PL) SQL

PlSql is the development language of the oracle database. SQL was designed from the start to operate on SETS (ie parallel task) whereas PL/SQL brought a lot in terms of exception handling. PL/SQL...

Share this page:
Follow us:
Task Runner