CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_RANDOM
Version 11.1
 
General
Note: Deprecated. Use the methods in the DBMS_CRYPTO built-in package.
Source {ORACLE_HOME}/rdbms/admin/dbmsrand.sql
First Available 8.0
Dependencies
DBMS_COMPARISON ODM_MODEL_UTIL
DBMS_JDM_INTERNAL SDO_NETWORK_MANAGER_I
DBMS_PREDICTIVE_ANALYTICS SDO_ROUTER_PARTITION
DBMS_WORKLOAD_CAPTURE UTL_RECOMP
DBMS_WORKLOAD_REPLAY WWV_FLOW_SAMPLE_APP
DMP_SYS  
 
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;
SELECT dbms_random.normal
FROM DUAL;

/

/

SELECT ABS(dbms_random.normal)
FROM DUAL;

/

/
 
RANDOM

Generate Random Numeric Values
dbms_random.random RETURN BINARY_INTEGER;
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 Only Values
SELECT (1+ABS(MOD(dbms_random.random,100000)))
FROM DUAL;
 
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;

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
Terminate use of the Package 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;
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
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;

/

/
 
Creating Random Numbers Without DBMS_RANDOM Demo
Demo Procedure 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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [130 users online]    © 2010 psoug.org