Tags
Oracle database comes with its own Java Virtual Machine and since it has JVM we can run our Java Classes in the DB Server itself. Why would some one need to run java on DB server ? The best answer would be to have stored procedure in Java which can use JDBC to do some operation on database.
For more details on this subject please refer to http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm
In this blog I am taking to very very simple example to show how can we create a Java Stored Procedure and Run them.
So we start with a simple Java class ‘HelloWorld’ and also have a method which is public static [Required to make public static]
public class HelloWorld{ public static void sayHello() { System.out.println("Hello World"); } }
We are going to treat this method as our stored procedure. This is where we would like to put our jdbc code to manipulate DB as shown in the example http://docs.oracle.com/cd/B28359_01/java.111/b31225/cheight.htm
So now we have the Java Class ready to uploaded in Oracle Database. In $ORACLE_HOME/bin there loadjava tool, using which we can upload our java class into DB server.
[me@myhost]$ ./loadjava -u sys@myhost:1521:orcl-v -r -t HelloWorld.java Password: ******** arguments: '-u' 'me@myhost:1521:orcl' '-v' '-r' '-t' 'HelloWorld.java' creating : source HelloWorld loading : source HelloWorld resolving: source HelloWorld Classes Loaded: 0 Resources Loaded: 0 Sources Loaded: 1 Published Interfaces: 0 Classes generated: 0 Classes skipped: 0 Synonyms Created: 0 Errors: 0
Now lets create package def and package body for our Java Stored Procedure so that it can be called as normal PL/SQL.
create or replace PACKAGE hello as PROCEDURE sayHello; END hello;
PACKAGE HELLO compiled
create or replace PACKAGE BODY hello AS PROCEDURE sayHello as LANGUAGE JAVA NAME 'HelloWorld.sayHello()'; end hello;
PACKAGE BODY HELLO compiled
And its time to call our store procedure and test.
SQL> SET SERVEROUTPUT ON; SQL> CALL dbms_java.set_output(2000); Call completed. SQL> CALL hello.sayHello(); Hello World Call completed.