Solved

Oracle PL-SQL: Generate random number.

Posted on 2004-04-15
3
3,482 Views
Last Modified: 2009-03-12
Hi Everyone,

I'm learning PL-SQL.  How can I generate a random number using DBMS_RANDOM?

Thanks for your time.
ITOrBust
0
Comment
Question by:itorbust
3 Comments
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 20 total points
ID: 10837688
-- value( low => , high => )
-- trunc gives integer.

SQL> select trunc(dbms_random.value( 5, 87 )) from dual;

TRUNC(DBMS_RANDOM.VALUE(5,87))
------------------------------
                            19

SQL> select trunc(dbms_random.value( 5, 87 )) from dual;

TRUNC(DBMS_RANDOM.VALUE(5,87))
------------------------------
                             6

SQL> select trunc(dbms_random.value( 5, 87 )) from dual;

TRUNC(DBMS_RANDOM.VALUE(5,87))
------------------------------
                            43

SQL> select trunc(dbms_random.value( 5, 87 )) from dual;

TRUNC(DBMS_RANDOM.VALUE(5,87))
------------------------------
                            27
0
 
LVL 9

Assisted Solution

by:pratikroy
pratikroy earned 20 total points
ID: 10837852
well you can use other functions of DBMS_RANDOM as well (that would generate random numbers for you) :

select dbms_random.normal from dual;
select dbms_random.random from dual;
select dbms_random.value from dual;
select dbms_random.value(1,100) from dual;

You can choose any of these based on your requirements.

Apart from these, you might be interested in
select dbms_random.string('a',10) from dual;

to generate random passwords (may be ;))
0
 
LVL 2

Accepted Solution

by:
n4nazim earned 20 total points
ID: 10839421
Hi,

The DBMS_RANDOM package is used to produce random numbers. The random number generator should be initialized with a suitably large seed before it is used and terminated once it's no longer needed. Several functions can be used to return random numbers:

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_seed  BINARY_INTEGER;
BEGIN
  l_seed := TO_NUMBER(TO_CHAR(SYSDATE,'YYYYDDMMSS'));
  DBMS_RANDOM.initialize (val => l_seed);
  FOR cur_rec IN 1 ..10 LOOP
    DBMS_OUTPUT.put_line('----');
    DBMS_OUTPUT.put_line('random                     : ' || TO_CHAR(DBMS_RANDOM.random));
    DBMS_OUTPUT.put_line('value                      : ' || TO_CHAR(DBMS_RANDOM.value));
    DBMS_OUTPUT.put_line('value(low => 1, high => 10): ' || TO_CHAR(DBMS_RANDOM.value(low => 1, high => 10)));
  END LOOP;
  DBMS_RANDOM.terminate;
END;
/


However if generating a RANDOM NUMBER is the only requirement, Here is a package which DOES NOT uses DBMS_RANDOM BUT STILL generates random NUMBERS and STRING. I have also pasted some examples on how to call this package procs/funcs.

/*
------------------------------------------------------------------------------
Filename:  random.txt
Purpose:   Random number/ string generator package
Author:    Unknown
Original:  http://orafaq.net/scripts/sql/random.txt
Edits:
 19990908 Phil Rand <prand@spu.edu> Added functions rand_string(), smaller().
------------------------------------------------------------------------------
*/

create or replace package random
is
   procedure srand(new_seed in number);
   procedure get_rand(r OUT number);
   procedure get_rand_max(r OUT number, n IN number);
   function  rand return number;
   function  rand_max(n IN number) return number;
   function  rand_string(ssiz IN number) return varchar2;
   function  smaller(x IN number, y IN number) return number;
   pragma restrict_references(rand, WNDS);
   pragma restrict_references(rand_max, WNDS);
   pragma restrict_references(random, WNDS, RNPS);
   pragma restrict_references(rand_string, WNDS);
   pragma restrict_references(smaller, WNDS);
end random;
/

create or replace package body random
is
   multiplier   constant number := 22695477;
   increment    constant number := 1;
   "2^32"       constant number := 2 ** 32;
   "2^16"       constant number := 2 ** 16;
   "0x7fff"     constant number := 32767;
   Seed         number          := 1;

   function  smaller(x IN number, y IN number) return number is
   begin
      if x <= y then
          return x;
      else
          return y;
      end if;
   end smaller;

   function rand_string(ssiz IN number) return varchar2 is
     i      number;
     m      number;
     c      char;
     result varchar2(2000) := '';
   begin
      m := smaller(ssiz,2000);
      for i in 1..m loop
          c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);
          result := result || c;
        end loop;
      return result;
   end rand_string;

   procedure srand(new_seed in number) is
   begin
     Seed := new_seed;
   end srand;

   function rand return number is
   begin
     Seed := mod(multiplier * Seed + increment, "2^32");
     return bitand(Seed/"2^16", "0x7fff");
   end rand;

   procedure get_rand(r OUT number) is
   begin
     r := rand;
   end get_rand;

   function rand_max(n IN number) return number is
   begin
     return mod(rand, n) + 1;
   end rand_max;

   procedure get_rand_max(r OUT number, n IN number) is
   begin
     r := rand_max(n);
   end get_rand_max;

begin
   select userenv('SESSIONID')
   into   Seed
   from   dual;
end random;
/

-- Some examples:
select random.rand_max(10) from dual;
select random.rand_max(10) from dual;
select random.rand_string(20) from dual;
select random.rand_string(20) from dual;

Hope this helps,
Rgds,
Nazim M
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 12c patching 1 80
case statement in where clause with not exist 15 52
Oracle Verification of DataPump Export and Import 17 54
Oracle collections 15 17
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question