Home

The DBMS_RANDOM package provides a built-in random number generator.

# Using DBMS_RANDOM

  • The RANDOM function produces integers in the range [-2^^31, 2^^31).
  • The VALUE function produces numbers in the range [0,1) with 38 digits of precision.

DBMS_RANDOM can be explicitly initialized but does not require initialization before a call to the random number generator. It automatically initializes with the date, user ID, and process ID if no explicit initialization is performed.

If this package is seeded twice with the same seed, then accessed in the same way, it produces the same result in both cases.

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 an overload of SEED . To produce different output for every run, simply omit the seed call. Then the system chooses a suitable seed for you.

# Summary of DBMS_RANDOM subprograms

Subprogram Description
INITIALIZE procedure Initializes the package with a seed value. deprecated, use SEED procedure.
NORMAL function Returns random numbers in a normal distribution.
RANDOM function Generates a random number. deprecated, use NORMAL and VALUE function.
SEED procedure Resets the seed.
STRING function Gets a random string.
TERMINATE procedure Terminates package. deprecated.
VALUE function One version gets a random number greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal point (38-digit precision). The other version gets a random Oracle Database number x , where x is greater than or equal to a specified lower limit and less than a specified higher limit.

Notes:

  • The PLS_INTEGER and BINARY_INTEGER data types are identical. This document uses BINARY_INTEGER to indicate data types in reference information (such as for table types, record types, subprogram parameters, or subprogram return values), but may use either in discussion and examples.
  • The INTEGER and NUMBER(38) data types are also identical. This document uses INTEGER throughout.

# NORMAL function

This function returns random numbers in a standard normal distribution.

Syntax

1
2
DBMS_RANDOM.NORMAL
  RETURN NUMBER;

Return value

The random number, a NUMBER value

# SEED procedure

This procedure resets the seed used in generating a random number.

Syntax

1
2
3
4
5
DBMS_RANDOM.SEED (
   val  IN  BINARY_INTEGER);

DBMS_RANDOM.SEED (
   val  IN  VARCHAR2);

Parameters

SEED procedure parameters

Parameter Description
val Seed number or string used to generate a random number

Usage notes

The seed can be a string up to length 2000.

# STRING function

This function generates and returns a random string.

Syntax

1
2
3
4
DBMS_RANDOM.STRING
   opt  IN  CHAR,
   len  IN  NUMBER)
  RETURN VARCHAR2;

Parameters

STRING function parameters

Parameter Description
opt What the returning string looks like:‘u’, ‘U’ - Returning string is in uppercase alpha characters.‘l’, ‘L’ - Returning string is in lowercase alpha characters.‘a’, ‘A’ - Returning string is in mixed-case alpha characters.‘x’, ‘X’ - Returning string is in uppercase alpha-numeric characters.‘p’, ‘P’ - Returning string is in any printable characters.Otherwise the returning string is in uppercase alpha characters.
len Length of the returned string

Return value

A VARCHAR2 value with the random string

# TERMINATE procedure

This procedure is deprecated. Although currently supported, it should not be used. It would be called when the user is finished with the package.

Syntax

1
DBMS_RANDOM.TERMINATE;

# VALUE function

One version returns a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). The other version returns a random Oracle Database NUMBER value x , where x is greater than or equal to the specified low value and less than the specified high value.

Syntax

1
2
3
4
5
6
7
DBMS_RANDOM.VALUE
  RETURN NUMBER;

DBMS_RANDOM.VALUE(
  low  IN  NUMBER,
  high IN  NUMBER)
RETURN NUMBER;

Parameters

VALUE function parameters

Parameter Description
low Lower limit of the range in which to generate a random number
high Upper limit of the range in which to generate a random number

Return value

A NUMBER value that is the generated random number

# Comments

# Random Order

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select num from (
  select 1 as num from dual
  union all
  select 2 from dual
  union all
  select 3 from dual
  union all
  select 4 from dual
  union all
  select 5 from dual
) order by DBMS_RANDOM.VALUE;

/*
num
 2
 1
 3
 4
 5
 */

# Select Sample

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 select rownum, num from (
  select num from (
    select 1 as num from dual
    union all
    select 2 from dual
    union all
    select 3 from dual
    union all
    select 4 from dual
    union all
    select 5 from dual
  ) order by DBMS_RANDOM.VALUE
) where rownum <= 3;

/*
rownum num
 1	   5
 2	   1
 3	   4
*/

You can use SAMPLE for select some distinct sample, but which cannot use in a subquery.

1
2
--the 20 here is an approximate percentage, not the number of rows desired. In this case, since you have 100 rows, to get approximately 20 rows you ask for a 20% sample.
select * from som_edi_asn_header sample(20) where rownum <= 3;

The SEED(n) option allows you to get always the same sample

1
2
3
select *
  from som_edi_asn_header sample(10) seed(1)
 where rownum <= 3;
Edited on