Oracle DBMS_RANDOM
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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_WRR_INTERNAL KUPW$WORKER
DBMS_DISRUPT DBMS_WRR_STATE SDO_NETWORK_MANAGER_I
DBMS_SWAT_VER_INTERNAL DBMS_WRR_STATE_BASE UTL_RECOMP
DBMS_WORKLOAD_CAPTURE_LIB KUPC$QUE_INT XS_DIAG_INT
DBMS_WORKLOAD_REPLAY_LIB KUPV$FT_INT  
Documented Yes: Packages and Types Reference
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
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
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
Built-in Functions
Built-in Packages
Database Security
DBMS_CRYPTO
What's New In 21c
What's New In 23c