?
Solved

Oracle PL-SQL: Generate random number.

Posted on 2004-04-15
3
Medium Priority
?
3,545 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 80 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 80 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 80 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

752 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