How to Create and Run a Oracle Java Stored Procedure
Setting up the JavaVM
- Make sure the database is up and running.
- Connect as user SYS or SYSTEM.
- 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.
- 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. - 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.
- 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 VARCHAR2as 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
// Hello.java
public class Hello
{
public static String world()
{
return "Hello world";
}
}
