Re: Java stored procedure from Oracle - Help!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 01 Sep 1999 13:37:35 GMT
Message-ID: <37ce2882.96485178@newshost.us.oracle.com>

A copy of this was sent to David Hull <dehull_at_us.ibm.com> (if that email address didn't require changing) On Tue, 31 Aug 1999 15:47:52 -0500, you wrote:

>We are looking for anyone who has experience calling a Java program from
>
>within a stored procedure in Oracle. We've created a Java random number
>
>generator, but due to constraints with a tool that would need to load
>these numbers, we are required to do it through a stored procedure. We
>are really down to the wire on this. It needs to be done and tested by
>Friday. Thanks!

Well its pretty straight forward.

Typically you'll use loadjava to load the compiled code into the database. for example:

$ javac random.java
$ loadjava -user utils/utils -verbose random.class

That puts the code into the database. Then, you create the bindings to the java. For example, some of mine are:

create or replace package pkg_name
as

    function connect_to( p_hostname in varchar2,

                         p_portno   in  number ) return number

    as language java
    name 'jsock.java_connect_to( java.lang.String, int ) return int';

    procedure send( p_sock in number, p_data in raw )     as language java
    name 'jsock.java_send_data( int, byte[] )';

    ....
end;

That makes it so I can call a procedure java_connect_to in the jsock class as a stored procedure now by invoking pkg_name.connect_to().

OTOH -- there is a builtin package dbms_random. you could use that instead. to install it connnect as INTERNAL and run the following:

SVRMGR> connect internal
Connected.

SVRMGR> @dbmsoctk
SVRMGR> @prvtoctk.plb
SVRMGR> @dbmsrand
SVRMGR> 

OTOH -- if you want to do it yourself and can use plsql, you can use the following:

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://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Sep 01 1999 - 08:37:35 CDT