Search the Site

Sponsors

bottom corner

Generating random numbers, characters, and dates with Oracle SQL

This note demonstrates how you can use Oracle's built-in dbms_random package to generate random values.

This page is filed under keyword(s): oracle.

Please review the following series of samples for how the dbms_random package works.

Random number:

select dbms_random.random as rand_numb from dual;

 RAND_NUMB
----------
-1.642E+09

Random number between 0 and 1:

select dbms_random.value as rand_numb_btwn_0_1 from dual;

RAND_NUMB_BTWN_0_1
------------------
        .950707301

Random number between x and y:

select dbms_random.value(10,20) as rand_numb_btwn_10_20 from dual;

RAND_NUMB_BTWN_10_20
--------------------
          14.6714295

Random upper case letters:

select dbms_random.string('U',10) as rand_upper from dual;

RAND_UPPER
----------
WVWZVYKNKP

Random lower case letters:

select dbms_random.string('L',10) as rand_lower from dual;

RAND_UPPER
----------
jdoyttefuw

Random mixed case letters:

select dbms_random.string('A',10) as rand_mixed from dual;

RAND_MIXED
----------
UBagEltDAr

Random upper case alphanumeric characters:

select dbms_random.string('X',10) as rand_upper_alphanumeric from dual;

RAND_UPPER_ALPHANUMERIC
-----------------------
JSANG89O8S

Random characters:

select dbms_random.string('P',10) as rand_chars from dual;

RAND_CHARS
----------
}![ NSOoo1

Random date between 1 Oct 1920 and 15 Mar 2015:
Note: The values used, 2422599 and 2457085, are Julian date numbers.

select to_date(trunc(dbms_random.value(2422599,2457085)),'j') as rand_date from dual;

RAND_DATE
---------
07-NOV-62

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 17 Dec 2008

bottom corner