Solved

Oracle PL-SQL: Generate random number.

Posted on 2004-04-15
3
3,504 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

733 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