Oracle | DBMS

Oracle DBMS_RANDOM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows

Purpose Generation of random strings and numbers

Note When possible it is preferable to use the functionality in DBMS_CRYPTO

AUTHID DEFINER

Dependencies

DBMS_AUTO_CLUSTERING_INTERNAL	DBMS_WRR_STATE
DBMS_COMPARISON	DUAL
DBMS_DISRUPT	KUPC$QUE_INT
DBMS_RCVCAT	KUPV$FT_INT
DBMS_SAGA_ADM_SYS	KUPW$WORKER
DBMS_SWAT_VER_INTERNAL	RDF_APIS_INTERNAL
DBMS_WORKLOAD_CAPTURE_LIB	SDO_NETWORK_MANAGER_I
DBMS_WORKLOAD_REPLAY_LIB	UTL_RECOMP
DBMS_WRR_INTERNAL	XS_DIAG_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);

      

      PL/SQL procedure successfully completed.

NORMAL

Returns random numbers in a standard normal distribution dbms_random.normal RETURN NUMBER PARALLEL_ENABLE;

SELECT dbms_random.normal;

      

          NORMAL

      ----------

      -.20965566

      

      /

      

          NORMAL

      ----------

      -.73784049

      

      SELECT ABS(dbms_random.normal);

      

      ABS(DBMS_RANDOM.NORMAL

      ----------------------

                  .637219097

      

      /

      

      ABS(DBMS_RANDOM.NORMAL

      ----------------------

                  .775053659

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))); (1+ABS(MOD(dbms_random.random,100000))) --------------------------------------- 38600

RECORD_RANDOM_NUMBER

Officially undocumented: External C function to record random value dbms_random.record_random_number(val IN NUMBER);

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;

      /

      

      PL/SQL procedure successfully completed.

      

      -- which all looks good except that it doesn't return a value in PL/SQL

      -- see the note to the left

REPLAY_RANDOM_NUMBER

Officially undocumented: External C function to replay random value dbms_random.replay_random_number(RETURN NUMBER;

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

      

      PL/SQL procedure successfully completed.

Overload 2 dbms_random.seed(val IN VARCHAR2);

exec dbms_random.seed('o42i4p');

      

      PL/SQL procedure successfully completed.

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;

exec dbms_random.terminate;

      

      PL/SQL procedure successfully completed.

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;

col value format 9999999999

      

      SELECT dbms_random.value;

      

        VALUE

      -------

            1

      

      /

      

        VALUE

      -------

            1

      

      col value format 9.999999999

      

      /

      

            VALUE

      -----------

       .051416867

      

      /

      

            VALUE

      -----------

       .385906481

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

      

      /

      

      /

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

Demo

The PL/SQL at right, in a LOOP, will generate random numbers without using DBMS_RANDOM or DBMS_CRYPTO based on a seed value selected from a table or provided through an input parameter IF seed=0 THEN seed := EXP(TO_NUMBER(TO_CHAR(SYSDATE,'ss'))/59); END IF; seed := 1/(seed - TRUNC(seed)); seed := seed - TRUNC(seed);