How to build a Java app and load it into an Oracle DB

Mar 8, 2012 | Databases, java, JDeveloper, Oracle, oracle pl/sql

Create the Java class

[java]public class Hello2 {

public static String hello() {
return “Hello World.”;
}

public static String hello(String name) {
return “Hello ” + name + “.”;
}

public static void main(String[] args) {
System.out.println(Hello2.hello());
System.out.println(Hello2.hello(“Java”));
}
}[/java]

Test the output (console):

[bash]java Hello[/bash]
Output:
[text]Hello World.
Hello Java.[/text]
After compilation, run the following (via puntty on DB server) to load it into the Database (console)

[bash]loadjava -r -f -o -user dbuser/dbpass HelloWorld2.class
# run dropjava to unload any loaded java app/class[/bash]
Calling it from JDeveloper:
[sql]–Create a db connection in JDev as SYS
–R-click on this new connection and select “SQL Worksheet”
–Enter the following in command window:
call dbms_java.LOADJAVA(‘/home/oracle/myapp.jar -verbose -force -grant SCOTT -resolve’);
–to Drop it:
call dbms_java.DROPJAVA(‘/home/oracle/myapp.jar -verbose’);[/sql]

Using a Package (see for Function further down):

[sql]–Create PL/SQL wrapper package bind to a Java class file
CREATE OR REPLACE PACKAGE hello2 AS
–Null argument function header
FUNCTION hello RETURN VARCHAR2;
–One argument function header
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
END hello2;
/
–Package body
CREATE OR REPLACE PACKAGE BODY hello2 AS
–Null argument function body
FUNCTION hello RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello() return String’;
–One argument function body
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello(java.lang.String) return String’;
END hello2;
/[/sql]

Now check that the objects are present (sql):

[sql]SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN (‘Hello2’, ‘HELLO2’);[/sql]

The output should be something like:

[text]OBJECT_NAME  OBJECT_TYPE   STATUS
———–  ————  ——
HELLO2 PACKAGE VALID
HELLO2 PACKAGE BODY VALID
Hello2 JAVA CLASS VALID[/text]

Finally, test the package by calling:

[sql]SELECT  hello2.hello(‘Mr Java’) FROM dual;[/sql]

Output:

[text]HELLO2.HELLO(‘MRJAVA’)
———————————–
Hello Mr Java.[/text]

Using a PL/SQL Function rather than a Package

[sql]create or replace function hello2 return varchar2 as
language java name ‘Hello2.hello() return java.lang.String’;[/sql]

To call the function, from sqlplus set a variable and dump the string in there:
[sql]variable tmp varchar2(20);
call hello2.hello() into :tmp;[/sql]
Output:
[text]tmp
———
Hello World.[/text]