Re: How to get RANDOM number?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 09 Jun 1999 11:36:57 GMT
Message-ID: <376051bc.85154415@newshost.us.oracle.com>

A copy of this was sent to violin.hsiao_at_mail.pouchen.com.tw (Violin) (if that email address didn't require changing) On 9 Jun 1999 00:56:16 GMT, you wrote:

>Hello,
>If I have a table
>TEST
>---------------------------------
>SER_NO NUMBER(5);
>NAME CHAR(20);
>
>I want to create a trigger before insert into TEST,
>and get a random number between 00001 and 99999 for SER_NO.
>CREATE TRIGGER INS_TEST
>BEFORE INSERT ON TEST FOR EACH ROW
>BEGIN
> :new.ser_no := (How to get the random number?)
>END;
>/
>
>Best Regards.
>
>Violin.
>violin.hsiao_at_mail.pouchen.com.tw

In 8.0 and up there is the dbms_random package. Before that you can use something like:

create or replace package random
is

        pragma restrict_references( random, WNDS, RNPS );

        procedure srand( new_seed in number );

	function rand return number;
	pragma restrict_references( rand, WNDS  );

	procedure get_rand( r OUT number );

	function rand_max( n IN number ) return number;
	pragma restrict_references( rand_max, WNDS);

	procedure get_rand_max( r OUT number, n IN number );

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;
--
	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;
/

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/

Current article is "Fine Grained Access Control", added June 8'th  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jun 09 1999 - 06:36:57 CDT