Solved

Oracle PL-SQL: Generate random number.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now