Re: Java stored procedure from Oracle - Help!
Date: Wed, 01 Sep 1999 13:37:35 GMT
Message-ID: <37ce2882.96485178_at_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 numberas 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> _at_dbmsoctk SVRMGR> _at_prvtoctk.plb SVRMGR> _at_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 CorporationReceived on Wed Sep 01 1999 - 15:37:35 CEST