Oracle PL/SQL/System Packages/DBMS RANDOM

Call dbms_random.normal to get a random number

   
SQL>
SQL>
SQL> begin
  2        for i in 1 .. 10 loop
  3          dbms_output.put_line( dbms_random.normal );
  4        end loop;
  5      end;
  6      /
1.54614544732963133287654886045271895089
.4317388077546154281139738992526089097407
-.3932262942586239869251307222842090454052
-1.6670328488404622986050198363653775926
.6688556901515673452664296942201359807998
1.33788099021421741537616822012046180008
.4640589645668300518621566089428065420796
-.2593943569301190968371056213308217873704
-.928080380810723360417809948629051042821
.3610354349426925924563975682781230663073
PL/SQL procedure successfully completed.
SQL> --

dbms_random.value

   
SQL>
SQL>  set serverout on
SQL>  exec dbms_output.put_line( dbms_random.value );
.02384074069949541946799357018112732833
PL/SQL procedure successfully completed.
SQL>
SQL>  set serverout on
SQL>  exec dbms_output.put_line( dbms_random.value );
.9204517146492220886865737108916710535
PL/SQL procedure successfully completed.
SQL> --

Insert random numbers into a table

   
SQL>
SQL> create table t( c1 int, c2 int, c3 int, c4 int ) storage ( freelists 10 );
Table created.
SQL>
SQL>
SQL> set echo on
SQL>
SQL>
SQL>      declare
  2           myNumber number;
  3       begin
  4           for i in 1 .. 10
  5           loop
  6               myNumber := dbms_random.random;
  7               insert into t values( myNumber, myNumber, myNumber, myNumber );
  8           end loop;
  9           commit;
 10       end;
 11  /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
        C1         C2         C3         C4
---------- ---------- ---------- ----------
-1.731E+09 -1.731E+09 -1.731E+09 -1.731E+09
-1.831E+09 -1.831E+09 -1.831E+09 -1.831E+09
1272757711 1272757711 1272757711 1272757711
 291563057  291563057  291563057  291563057
1906157085 1906157085 1906157085 1906157085
 180671850  180671850  180671850  180671850
1011541972 1011541972 1011541972 1011541972
-1.686E+09 -1.686E+09 -1.686E+09 -1.686E+09
-863840673 -863840673 -863840673 -863840673
1602415758 1602415758 1602415758 1602415758
10 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL> --

Insert random value to table

  
SQL>
SQL> create table emp (
  2  id number(6) );
Table created.
SQL>
SQL> alter table emp
  2  add constraint emp_pk
  3  primary key (id);
Table altered.
SQL>
SQL>
SQL> create or replace procedure gen_emp is
  2   v_new_cid emp.id%type;
  3  begin
  4   loop
  5    begin
  6     v_new_cid := round(dbms_random.value(1000000,9999999));
  7     insert into emp values (v_new_cid);
  8     exit;
  9    exception when dup_val_on_index then
 10     null;
 11    end;
 12   end loop;
 13  end;
 14  /
Procedure created.
SQL>
SQL>
SQL> drop table emp;
Table dropped.

mod(abs(dbms_random.random),50000)+1

   
SQL>
SQL> create table random ( x int );
Table created.
SQL>
SQL> begin
  2          for i in 1 .. 100000
  3          loop
  4                  insert into random values
  5                  ( mod(abs(dbms_random.random),50000)+1 );
  6          end loop;
  7  end;
  8  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL> drop table random;
Table dropped.
SQL> --

Random string with dbms_random.string

  
SQL> COLUMN mixed_up NEW_VALUE mixed_up_sub_var
SQL> SELECT dbms_random.string("X",30) mixed_up
  2  FROM dual;
3X0P1I67MKLZM7934O6JGUJSE3Q9A2
1 row selected.
SQL>
SQL> DEFINE mixed_up_sub_var
DEFINE MIXED_UP_SUB_VAR = "3X0P1I67MKLZM7934O6JGUJSE3Q9A2" (CHAR)
SQL>
SQL>
SQL>

ROUND a DBMS_RANDOM.VALUE

   
SQL>
SQL> CREATE OR REPLACE FUNCTION get_num (
  2     p_highval   NUMBER,
  3     p_lowval    NUMBER := 0,
  4     p_scale     PLS_INTEGER := 0
  5  )
  6     RETURN NUMBER
  7  IS
  8     l_ret   NUMBER;
  9  BEGIN
 10     l_ret := ROUND (DBMS_RANDOM.VALUE (p_lowval, p_highval), p_scale);
 11     RETURN l_ret;
 12  END;
 13  /
Function created.
SQL>
SQL> select get_num(5,2,2) from dual;
GET_NUM(5,2,2)
--------------
          4.27

Round a random value from dbms_random.value

   
SQL>
SQL>
SQL>     begin
  2        for i in 1 .. 10 loop
  3          dbms_output.put_line( round( dbms_random.value*100 ) );
  4        end loop;
  5      end;
  6      /
63
62
7
72
82
80
88
42
30
67
PL/SQL procedure successfully completed.
SQL>
SQL> --

Seed a random value with dbms_random.seed

   
SQL>
SQL>  set serverout on
SQL>  exec dbms_random.seed( to_char( sysdate, "YYYYMMDD" ) );
PL/SQL procedure successfully completed.
SQL>  exec dbms_output.put_line( dbms_random.value );
.09697957992149808336891757660029645577
PL/SQL procedure successfully completed.
SQL>  exec dbms_output.put_line( dbms_random.value );
.07915501486927581930051571448998022468
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>  set serverout on
SQL>  exec dbms_random.seed( to_char( sysdate, "YYYYMMDD" ) );
PL/SQL procedure successfully completed.
SQL>  exec dbms_output.put_line( dbms_random.value );
.09697957992149808336891757660029645577
PL/SQL procedure successfully completed.
SQL>  exec dbms_output.put_line( dbms_random.value );
.07915501486927581930051571448998022468
PL/SQL procedure successfully completed.
SQL> --

This script illustrates the use of the DBMS_RANDOM package.

   
SQL>
SQL> set serveroutput on
SQL>
 First call RANDOM without initializing the seed.  This will
 raise ORA-1426.
SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
  3  END;
  4  /
503520765
PL/SQL procedure successfully completed.
SQL>
 Now initialize, and print out 10 random numbers.
SQL> BEGIN
  2    DBMS_RANDOM.INITIALIZE(12345);
  3    FOR v_Count IN 1..10 LOOP
  4      DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);
  5    END LOOP;
  6  END;
  7  /
-1817329670
-610488908
1775775349
-1974231734
-861483099
1044932800
1916512750
808190358
-1880806196
-1406274277
PL/SQL procedure successfully completed.
SQL>

trunc a random value from dbms_random.value

   
SQL>
SQL>  begin
  2        for i in 1 .. 10 loop
  3          dbms_output.put_line( trunc( dbms_random.value( 1, 101 ) ) );
  4        end loop;
  5      end;
  6      /
15
17
28
52
25
19
55
35
18
48
PL/SQL procedure successfully completed.
SQL> --

Use dbms_random.string to create random string

   
SQL>
SQL>
SQL> exec dbms_output.put_line( dbms_random.string( "A", 20 ) );
iRhhzYvyWLroMPNCvSdx
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "x", 20 ) );
V5HN9YL3C6GJN9BNP4R9
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "P", 20 ) );
w6aH% ZB[3bjH[;x;6h@
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "l", 20 ) );
rjncmdlykahvpgjyecax
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line( dbms_random.string( "u", 20 ) );
EHXUUTMYMBAJMHIKKDJM
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --