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