Oracle DBMS_RANDOM
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Generation of random strings and numbers
Note When possible it is preferable to use the functionality in DBMS_CRYPTO
AUTHID DEFINER
Dependencies
DBMS_COMPARISON DBMS_WORKLOAD_CAPTURE_LIB DBMS_WRR_STATE
DBMS_DISRUPT DBMS_WORKLOAD_REPLAY SDO_NETWORK_MANAGER_I
DBMS_GSM_POOLADMIN DBMS_WORKLOAD_REPLAY_LIB UTL_RECOMP
DBMS_WORKLOAD_CAPTURE DBMS_WRR_INTERNAL XS_DIAG_INT
Documented Yes
First Available 8.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsrand.sql
Subprograms
 
INITIALIZE
Initialize package with a seed value dbms_random.initialize (seed IN BINARY_INTEGER);
exec dbms_random.initialize(17809465);
 
NORMAL
Returns random numbers in a standard normal distribution  dbms_random.normal RETURN NUMBER PARALLEL_ENABLE;
SELECT dbms_random.normal
FROM dual;

/

/

SELECT ABS(dbms_random.normal)
FROM dual;

/

/
 
RANDOM
Generate Random Numeric Values dbms_random.random RETURN BINARY_INTEGER PARALLEL_ENABLE;
conn sys@pdbdev as sysdba

set serveroutput on

DECLARE
 x  PLS_INTEGER;
 rn NUMBER(20);
BEGIN
  SELECT hsecs
  INTO rn
  FROM gv$timer;

  dbms_random.initialize(rn);
  FOR i IN 1..20
  LOOP
    x := dbms_random.random;
    dbms_output.put_line(x);
    rn := x;
  END LOOP;
  dbms_random.terminate;
END;
/
Force Output To Positive Values SELECT (1+ABS(MOD(dbms_random.random,100000)))
FROM dual;
 
RECORD_RANDOM_NUMBER (new 12.1)
Officially undocumented: External C function to record random value dbms_random.record_random_number(val IN NUMBER);
PRAGMA restrict_references (record_random_number, WNDS);
DECLARE
 x NUMBER;
BEGIN
  dbms_random.record_random_number(42);
  x := dbms_random.replay_random_number;
  dbms_output.put_line('Output: ' || TO_CHAR(x));
END;
/

-- which all looks good except that it doesn't return the number;
 
REPLAY_RANDOM_NUMBER (new 12.1)
Officially undocumented: External C function to replay random value dbms_random.replay_random_number(RETURN NUMBER;
PRAGMA restrict_references (replay_random_number, WNDS);
See RECORD_RANDOM_NUMBER Demo Above
 
SEED
Reset the seed value

Overload 1
dbms_random.seed(val IN BINARY_INTEGER);
exec dbms_random.seed(681457802);
Overload 2 dbms_random.seed(val IN VARCHAR2);
exec dbms_random.seed('o42i4p');
 
STRING
Create Random Strings dbms_random.string(opt IN CHAR, len IN NUMBER) RETURN VARCHAR2 PARALLEL_ENABLE;

opt seed values:
'a','A'  alpha characters only (mixed case)
'l','L'  lower case alpha characters only
'p','P'  any printable characters
'u','U'  upper case alpha characters only
'x','X'  any alpha-numeric characters (upper)
CREATE TABLE random_strings AS
SELECT rownum RNUM,
dbms_random.string('A', 12) RNDMSTR
FROM all_objects
WHERE rownum <= 200;

col rndmstr format a20

SELECT * FROM random_strings;
-- create test data
CREATE TABLE test (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

DECLARE
 x VARCHAR2(20);
 y VARCHAR2(20);
BEGIN
  FOR i IN 1..100
  LOOP
    x := dbms_random.string('A', 20);
    y := dbms_random.string('A', 20);

    INSERT INTO test
    (col1, col2)
    VALUES
    (x,y);
  END LOOP;
  COMMIT;
END;
/

SELECT * FROM test;
 
TERMINATE
Reset the package ... essentially each call makes it serially reusable by resetting internal variables dbms_random.terminate;
dbms_random.terminate;
 
VALUE
Gets a random number, greater than or equal to 0 and less than 1, with decimal 38 digits

Overload 1
dbms_random.value RETURN NUMBER PARALLEL_ENABLE;
SELECT dbms_random.value
FROM dual;

/

/
Alternatively, you can get a random Oracle number x, where x is greater than or equal to low and less than high

Overload 2
dbms_random.value(low NUMBER, high NUMBER) RETURN NUMBER PARALLEL_ENABLE;
SELECT dbms_random.value(2, 3)
FROM dual;

/

/
Select a random record SELECT srvr_id
FROM (
  SELECT srvr_id
  FROM servers
  ORDER BY dbms_random.value)
WHERE rownum = 1;

/

/
 
Demo
Create random numbers without using DBMS_RANDOM or DBMS_CRYPTO IF seed=0 THEN
  seed := EXP(TO_NUMBER(TO_CHAR(SYSDATE,'ss'))/59);
END IF;
seed := 1/(seed - TRUNC(seed));
seed := seed - TRUNC(seed);

Related Topics
DBMS_CRYPTO
Packages
Security
What's New In 12cR1
What's New In 12cR2