Solved

Oracle PL-SQL: Generate random number.

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

688 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