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 is now availble. If you haven't you downloaded already: Why?

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