Oracle | DBMS

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	REPLAY_RANDOM_NUMBER	TERMINATE
NORMAL	SEED	VALUE
RANDOM	STRING	Demo
RECORD_RANDOM_NUMBER

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);