Oracle and Java Stored Procedure

Starting with Oracle 8i, Oracle RDBMS server has a Java Virtual Machine integrated in it. This means that it can run stored procedures/programs written in java directly in the database. This has huge benefits for PL/SQL language. We can easily add new functionalities in Oracle. In the following paragraphs I'll create a simple class which has a method with one parameter of type java.lang.String. This method will be used as an Oracle stored procedure.

I suppose you use eclipse IDE. Create a Java project. Add a library reference for ojdbc14.jar(this is oracle jdbc driver). Now, we can start writting the class.

Step 1:

package ro.teste.sp;

public class TesteSP
{
public static void WriteMessage(String msg) throws SQLException
{
Connection con = DriverManager.getConnection("jdbc:default:connection");

System.out.println("We received from PL/SQL: " + msg);
}
}

Step 2:

loadjava -user user/passwd@db TesteSP.java

Step 3:

Se creaza un wrapper in baza de date.

We create a wrapper procedure in PL/SQL. We do this so we can invoke the previous java stored procedure natively.

CREATE OR REPLACE PROCEDURE WrapJava1(msg VARCHAR2)
AS
LANGUAGE java
NAME 'ro.teste.sp.TesteSP.WriteMessage(java.lang.String)';

Step 4:

CALL WrapJava1('Hello world');

Comments:

This example is extremely simple. You can create very complicated java stored procedure. For instance, I have managed to write a stored procedure that was generating a jpg image base on received parameters. I have also written a stored procedure to get latest currency from a specified web service. The list of examples can continue indefinitely. I hope you can find this feature of Oracle useful.