Re: Unix to Oracle

ccoplan_at_infi.net (Chris Coplan) wrote:

>I need to use a random number in a where clause (so that I may test a
>randomly selected subset of my population). To my knowledge, Oracle
>doesn't have anything built in....is there a way that Oracle can get a
>number (or list of numbers) from the host?
 

>I'm running Oracle on a HP Unix box, and have written a shell script to
>generate the random #'s for me.
 

>Any suggestions appreciated....
 

>Thx...ccoplan_at_infi.net
>Chris Coplan

If you are using Oracle 7.1 the following pl/sql, which is callable from SQL in a SELECT, or WHERE (or even insert/update/delete), mimicks the C rand() function exactly. At least on my pc, a C program printing out rand and this pl/sql printing out rand generate the same sequence given the same seed. Hope this helps.

(btw, to all, including DB versions would be good in all notes)

create or replace package random
is

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

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
	srand( userenv( 'SESSIONID' ) );
end random;
/
grant execute on random to public
/
create public synonym random for random
/




Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government