Invoking Java from PL/SQL

After struggling for few days to enable Java PL/SQL integration... At last, I managed to make it work!

Ok, let's get down to business ..

I need to create a Java Stored procedure that will be called by PL/SQL. This Java method needs to return a collection of POJOs.

To create a Java method that returns basic Java data type to PL/SQL is very easy. Since Oracle already provides default data type mapping from basic Java data type to basic SQL type, such as java.lang.String to VARCHAR2.

But, what to do if you need to return a collection of complex POJOs?
Well .. we will go through it now ..

There are 2 difficulties to implement this approach
1 - How to map a Java POJO with SQL Data type?
2 - How to return a collection of POJOs to PL/SQL?

For this purpose, I have separated 2 sections to explain it, well .. 3 sections actually

Overview
Our company asked me to do POC on whether we can implement new application using Oracle Forms that calls Java module in the backend.
Theoretically, we can achieve this by utilizing Oracle JVM, whereby our PL/SQL calls the loaded Java libraries. The Oracle Forms will then call the respective PL/SQL.
But, this is still in theory, that is why we need to do POC to ensure it is working.

Requirements

• Ensure your Oracle DB Server version is 9i or above.


• Ensure your Oracle Client has "loadjava" and "dropjava" tools installed - they are part of Oracle Development Java utilities section during Oracle Client installation.

Develop and bind the Java Bean into Oracle DBMS

• Creates the POJO bean (JAdviser.java) by implementing SQLData and Serializable interfaces.


• Compile the bean and load it into Oracle JVM using "loadjava" utility.


• Creates new Oracle TYPE (ADVISER) to bind this POJO bean in the database.

The source codes are like below

---

Java bean

package test;

import java.io.Serializable;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class JAdviser implements SQLData, Serializable {
    
    /**
    * Default UID given by Eclipse.
    */
    private static final long serialVersionUID = 1L;
    
    private String agentKey;
    
    // Ensure you have the value here, Capital, and not null.
    private String sql_type = "ADVISER";
    
    public JAdviser() {}
    
    public JAdviser(String key) {
        this.agentKey = key;
    }
    
    public String getKey() {
        return agentKey;
    }
    
    public void setKey(String agentKey) {
        this.agentKey = agentKey;
    }
    
    public String getSQLTypeName() throws SQLException {
        return sql_type;
    }
    
    public void readSQL(SQLInput stream, String typeName) throws SQLException {
        sql_type = typeName;
        agentKey = stream.readString();
    }
    
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeString(agentKey);
    }
}


PL/SQL to bind the Java bean

CREATE or REPLACE TYPE adviser AS OBJECT
EXTERNAL name 'test.JAdviser' LANGUAGE JAVA USING SQLData
(
agentKey varchar2(100)
external name 'java.lang.String',

constructor function adviser
return self as result,

member function getKey
return varchar2 as language java
name 'test.JAdviser.getKey() return java.lang.String',

member function getSQLTypeName
return varchar2 as language java
name 'test.JAdviser.getSQLTypeName() return java.lang.String'
);


Test the object has been properly registered inside Oracle

SELECT object_name, object_type, status
FROM user_objects
WHERE object_name LIKE '%JAdviser';

---

Develop the Java Stored Procedure and bind it

• Creates the Java stored proc (TestFunction.java)
• Compile and load it into Oracle JVM using "loadjava" utility.
• Creates new Oracle TYPE (ADVISERLIST) to hold the returned Java collection.

Create Java stored Procedure
This is the actual Java stored procedure that constructs the Java bean, return
the collection of the Java bean to the PL/SQL.

package test;

import java.sql.SQLException;

import oracle.jdbc.OracleDriver;
import oracle.jdbc.driver.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestFunction {
    
    public static oracle.sql.ARRAY getAdvisers(String tableName)
    throws SQLException {
        String agentKeys[] = new String[] {"101", "202", "303"};
        JAdviser[] adviserList = new JAdviser[agentKeys.length];
        
        for (int iCnt = 0; iCnt < agentKeys.length; iCnt++) {
            JAdviser adviser = new JAdviser();
            adviser.setKey(agentKeys[iCnt]);
            adviserList[iCnt] = adviser;
        }
        
        // Built-in connection when running in the database.
        OracleConnection conn = (OracleConnection) new OracleDriver()
        .defaultConnection();
        
        // Bind the Java array to the database UDT.
        ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
        "ADVISERLIST", conn);
        
        System.out
        .println("Descriptor type: " + arrayDescriptor.getArrayType());
        
        // Create the return array.
        return new ARRAY(arrayDescriptor, conn, adviserList);
    }
    
}


Creates the "container" to hold the returned Java Collection
As you know, we need to create a SQL TYPE to hold the collection

CREATE or REPLACE TYPE ADVISERLIST is TABLE OF adviser;


PL/SQL to bind the Java stored procedure in DBMS

CREATE OR REPLACE FUNCTION
testFunction (tableName varchar2) RETURN ADVISERLIST
AS LANGUAGE JAVA
NAME 'test.TestFunction.getAdvisers (java.lang.String) return oracle.sql.ARRAY';


---

Next, just test this new function by calling it directly:

SELECT testFunction ('dummy') FROM DUAL;

That's all, it is as simple as that.
However, please note these hints below to make it work

Hints

• All Java methods must be public static except if the Java Class implements SQLData interface


• It is strongly suggested, to develop Java stored procedure or any Java objects that shall be called by PL/SQL, to implement java.sql.SQLData interface or Oracle Datum interfaces, to keep the nature of its Object Oriented approach


• Implementing SQLData interface requires you to properly implement readSQL(...) and writeSQL(...) methods, plus getSQLTypeName() which returns the SQL TYPE name of this object in Capitals and not null!


• To return 2 objects or more from Java stored procedure to PL/SQL, the Java return type must be in Array, either oracle.sql.ARRAY or java.sql.Array


• SQL Type inside the PL/SQL that receives that array shall be Nested Table for easier development, syntax: TYPE ... is TABLE OF ...