How to Create and Run a Oracle Java Stored Procedure

How to Create and Run a Oracle Java Stored Procedure

Setting up the JavaVM

1. Make sure the database is up and running.
2. Connect as user SYS or SYSTEM.
3. Perform the following queries to make sure that your JavaVM is installed and correctly initialized.

SQL> DESC DBMS_JAVA

SQL> SELECT table_name
FROM user_tables
WHERE table_name LIKE '%JAVA%';

SQL> SELECT count(*)
FROM dba_objects
WHERE object_type LIKE '%JAVA%';

Note : If any of these queries return no data the JavaVM is not installed correctly in the oracle database.

Check the installed JDK in the ORACLE_HOME.

1. Check that you have JDK installed and that it is in the path environment variable by executing following commands:
   
   > which java
> which javac

   
   These commands will return something similar to:
   
   > /bin/java
> /bin/javac

Creating Java stored procedures.

Create a Java stored procedures in the database
There are two ways to create a java stored procedure. The first one is to create a so called source class (*.java) outside the database, compile the class and load this class into the database. The second option is to create both the source and the compiled class within the database.

• Create a source code file outside the database.
  In our situation this class already exists as Hello.java (). As we only want to load the executable file (class-file) we first need to compile the source outside the database in a normal JDK environment. To prevent any errors from happening make sure to use the JDK supplied with the database.
1. Create the executable file of our example Hello.java.

> cd $ORACLE_HOME/javavm/examples/jsp/Hello.java
> javac Hello.java

Above steps will create a file called Hello.class.

2. In order to use the "class"-file we need to load this file into the database.

> $ORACLE_HOME/bin/loadjava -r -f -o -user scott/tiger Hello.class

• Create a source code file inside the database.
  When connected as user SCOTT execute following source code command. The database will create the executable file (class-file) automatically.

CREATE JAVA SOURCE NAMED "Hello"
AS
public class Hello {
public static String world() {
return "Hello World ";
}
};
/

Create a wrapper function.
A java stored procedure cannot be called directly from SQL or PL/SQL. In order to do so a so called wrapper function must be created. This wrapper function is an interface to access the java stored procedure. For our java example the wrapper function looks like:

CREATE OR REPLACE FUNCTION Hello RETURN VARCHAR2
as LANGUAGE JAVA NAME 'Hello.world() return String';
/

Test the wrapper function.
To check whether the java stored procedure and the wrapper function are installed correctly, execute following query:

SQL> select Hello from dual;

HELLO
--------------------------------------------------------------------------------
Hello world

Code for Hello.java

// Hello.java

public class Hello
{
public static String world()
{
return "Hello world";
}
}