HOW CALL JAVA METHOD FROM ORACLE / Java Stored Procedure

Step-1: Create Java Project in eclipse. I use jdk  1.5 for oracle 11g.

Step-2: Form oracle we can call static method.  for the supported data type check this link . This link will be helpful to define parameter and return type. For the User Defined Data type we have  implement either the SQLData or CustomDatum interface.  

package com.abb.functions;

import java.sql.Array;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Dictionary;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;
import com.abb.functions.out.model.BranchOpenDateOut;


public class BranchWrapper {
    public synchronized static String isValidBranch(String branchCode) {
        return "CBS";
    }

    public static Array parseToArray(String str, String delim) throws SQLException {
        OracleDriver ora = new oracle.jdbc.OracleDriver();
        Connection conn = ora.defaultConnection();
        OracleConnection oraConn = (OracleConnection) conn;
        Array arr = oraConn.createARRAY("STRINGARRAY", str.split(delim));
        return arr;
    }
   
    public synchronized static BranchOpenDateOut isBranchOpenDate(String branchCode) throws SQLException {

        BranchOpenDateOut e = new BranchOpenDateOut("BRANCHOPENDATEOUT", "KAMAL HOSSAIN");       
        return e;
    }   


}

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleConnection;
import oracle.jpub.runtime.MutableStruct;
import oracle.sql.CustomDatum;
import oracle.sql.CustomDatumFactory;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

public BranchOpenDateOut  implements CustomDatum, CustomDatumFactory {
    public static final String _SQL_NAME = "BranchOpenDateOut";
    public static final int _SQL_TYPECODE = oracle.jdbc.OracleTypes.STRUCT;

    MutableStruct _struct;

    static int[] _sqlType = {java.sql.Types.VARCHAR };

    static CustomDatumFactory[] _factory = new CustomDatumFactory[_sqlType.length];

    static final TRANSACTIONOUT _BranchOpenDateOutFactory = new TRANSACTIONOUT();

    public static CustomDatumFactory getFactory() {
        return _BranchOpenDateOutFactory ;
    }

    /* constructor */
    public TRANSACTIONOUT() {
        _struct = new MutableStruct(new Object[_sqlType.length], _sqlType,
                _factory);
    }

    /* CustomDatum interface */
    public Datum toDatum(OracleConnection c) throws SQLException {
        return _struct.toDatum(c, _SQL_NAME);
    }

    /* CustomDatumFactory interface */
    public CustomDatum create(Datum d, int sqlType) throws SQLException {
        if (d == null) {
            return null;
        }
        TRANSACTIONOUT o = new TRANSACTIONOUT();
        o._struct = new MutableStruct((STRUCT) d, _sqlType, _factory);
        return o;
    }

    /* accessor methods */
    public String getmResBrDt() throws SQLException {
        return (String) _struct.getAttribute(0);
    }

    public void setmResBrDt(String mResBrDt) throws SQLException {
        _struct.setAttribute(0, mResBrDt);
    }
  
         
}

Step-3: after compiling this project export it as jar.

Step-4 use following loadjava command

Loadjava –user schema xyz jar-location

Step-5: in oracle create a Wrapper based on the static method want to call from oracle.

CREATE or REPLACE
FUNCTION ISVALIDBRANCH(brCode VARCHAR2) RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isValidBranch(java.lang.String) return java.lang.String';

CREATE or REPLACE

FUNCTION PARSETOARRAY (str IN VARCHAR2, delim IN VARCHAR2)
RETURN STRINGARRAY AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.parseToArray (java.lang.String, java.lang.String) return java.sql.Array';

CREATE or REPLACE

FUNCTION ISBRANCHOPENDATE(brCode VARCHAR2) RETURN BranchOpenDateOut AS
LANGUAGE JAVA NAME 'com.abb.functions.BranchWrapper.isBranchOpenDate2(java.lang.String) return com.abb.functions.out.model.BranchOpenDateOut';

We have to map BranchOpenDateOutin and  STRINGARRAY  oracle as Object

CREATE or REPLACE  TYPE BranchOpenDateOut AS OBJECT (
   mResBrDt VARCHAR2 (100)
)

CREATE or REPLACE type STRINGARRAY is table of varchar2(4000);

That it we have done now we are going to call JAVA METHODS from oracle.

DECLARE
  STR VARCHAR2(200);
  DELIM VARCHAR2(200);
  v_Return STRINGARRAY;

  v_Return2 ABBTEST.BRANCHOPENDATEOUT;

v_Return3 VARCHAR2(200);
BEGIN
 -- call to  parseToArray method

  STR := 'A,B';
  DELIM := ',';

  v_Return := PARSETOARRAY(STR => STR, DELIM => DELIM );

FOR i IN v_Return.first .. v_Return.last LOOP
     DBMS_OUTPUT.PUT_LINE(v_Return(i));
END LOOP;

-- call to  isBranchOpenDate method

v_Return2 := ISBRANCHOPENDATE(BRCODE => BRCODE);
DBMS_OUTPUT.PUT_LINE(v_Return2.mResBrDt);

-- call to  isValidBranch method

v_Return3 := ISVALIDBRANCH(BRCODE => BRCODE);
DBMS_OUTPUT.PUT_LINE(v_Return3);

  
END;