| General Information |
| 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 |
SDO_NETWORK_MANAGER_I |
| DBMS_WORKLOAD_CAPTURE |
UTL_RECOMP |
| DBMS_WORKLOAD_REPLAY |
WWV_FLOW_IMAGE_GENERATOR |
| DBMS_PROPAGATION_ADM |
WWV_FLOW_SAMPLE_APP |
| DMP_FMTLIB |
|
|
| Security Model |
Execute is granted to PUBLIC. Package is explicitly AUTHID DEFINER as of 11.2. |
| 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 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 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 |
| 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 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;
/
/ |
| |
| Creating Random Numbers Without DBMS_RANDOM Demo |
| Demo PL/SQL |
IF seed=0 THEN
seed := EXP(TO_NUMBER(TO_CHAR(SYSDATE,'ss'))/59);
END IF;
seed := 1/(seed - TRUNC(seed));
seed := seed - TRUNC(seed); |
