Chapter 24. DBMS_RANDOM

Table of Contents

24.1. Overview
24.2. Procedures and Functions
24.2.1. NORMAL
24.2.2. RANDOM
24.2.3. SEED
24.2.4. STRING
24.2.5. VALUE

This chapter briefly introduces the DBMS_RANDOM package, and describes how to use the procedures and functions of the package.

24.1. Overview

DBMS_RANDOM creates a random number.

24.2. Procedures and Functions

This section describes the procedures and functions provided by the DBMS_RANDOM package, in alphabetical order.

24.2.1. NORMAL

Extracts random values in a normal (Gaussian) distribution.

Details about the NORMAL function are as follows:

  • Prototype

    DBMS_RANDOM.NORMAL
    RETURN NUMBER;
  • Example

    DECLARE
            a NUMBER;
    BEGIN
            a := DBMS_RANDOM.NORMAL;
    END;
    /

24.2.2. RANDOM

Creates a random integer that is greater than or equal to -(2^31) and less than 2^31.

Details about the RANDOM function are as follows:

  • Prototype

    DBMS_RANDOM.RANDOM
    RETURN BINARY_INTEGER;
  • Example

    DECLARE
            a BINARY_INTEGER;
    BEGIN
            a := DBMS_RANDOM.RANDOM;
    END;
    /

24.2.3. SEED

Creates a new sequence for generating random values by specifying a new seed. If the existing seed value is used, the seed will be reset to the first value in the existing sequence, and the same random value will be outputted every time this procedure is called.

Details about the SEED procedure are as follows:

  • Prototype

    • When the seed is a number:

      DBMS_RANDOM.SEED 
      (                                                            
          val             IN              BINARY_INTEGER
      );
    • When the seed is a string:

      DBMS_RANDOM.SEED 
      (                                                            
          val             IN              VARCHAR2
      );
  • Parameter

    ParameterDescription
    valSeed number or a string used to create a random value.
  • Example

    DECLARE
            a BINARY_INTEGER;
    BEGIN
            DBMS_RANDOM.SEED(3);
            a := DBMS_RANDOM.NORMAL;
    
            DBMS_RANDOM.SEED('abc');
            a := DBMS_RANDOM.RANDOM;
    END;
    /

24.2.4. STRING

Creates a random string.

Details about the STRING function are as follows:

  • Prototype

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

    ParameterDescription
    opt

    Desired string format options:

    • 'u' or 'U': String in uppercase alpha characters

    • 'l' or 'L': String in lowercase alpha characters

    • 'a' or 'A': String in case-insensitive alpha characters

    • 'x' or 'X': String in uppercase alpha-numeric characters

    • 'p' or 'P': String in any printable characters

    By default, a string in uppercase alpha characters is returned.

    lenLength of the string to create.
  • Example

    DECLARE
            a varchar2(1000);
    BEGIN
            a := DBMS_RANDOM.STRING('x', 20);
            dbms_output.put_line(a);
    END;
    /

24.2.5. VALUE

Creates a random number within the specified range. If the range is not specified, a number greater than or equal to 0 and less than 1 is returned.

Details about the VALUE function are as follows:

  • Prototype

    • When the range is not specified:

      DBMS_RANDOM.VALUE
      RETURN NUMBER;
    • When the range is specified:

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

    ParameterDescription
    lowMinimum value of the range.
    highMaximum value of the range.
  • Example

    DECLARE
            a number;
    BEGIN
            a := DBMS_RANDOM.VALUE;
            a := DBMS_RANDOM.VALUE(-30, 100);
    END;
    /