Oracle DBMS_RANDOM
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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_REPLAY_LIB UTL_RECOMP
DBMS_REGISTRY_SYS DBMS_WRR_INTERNAL WWV_FLOW_API
DBMS_WORKLOAD_CAPTURE HTMLDB_UTIL WWV_FLOW_IMAGE_GENERATOR
DBMS_WORKLOAD_CAPTURE_LIB SDO_NETWORK_MANAGER_I XS_DIAG_INT
DBMS_WORKLOAD_REPLAY    
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 / 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

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved